Re: Delete row in a lookup table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Whether or not it's good practice depends on who you ask. :-)  

Every table should have a primary key.  Primary keys, however, may span 
multiple columns.  That's perfectly legal.  In some cases that primary key 
may span every column, but generally that's a sign of bad design unless 
you're talking about a table that just relates one table to another (many to 
many relation).

The question is whether it's better to have a surrogate key[1].  That is, a 
unique integer value that has no meaning itself beyond being a unique key.  
For example, in pretty much any authentication system the username will be 
unique, and therefore could easily be used as the primary key.  Other tables, 
then, would reference back to the user table using the username as the 
foreign key.  

There's pros and cons to surrogate keys over "natural" keys.  See more links 
below that I don't feel like repeating[2].  

Personaly I tend toward surrogate keys in most cases for entities, but not for 
relationships.  In your case, then, no, I would not have a surrogate key on 
the membersleagues table.  Instead I'd define (game_id, member_id) as the 
primary key.  You can absolutely then DELETE from membersleagues WHERE 
game_id=4 AND member_id=3.  Or just delete by one or the other.  You don't 
have to have a primary key defined in order to be able to DELETE, it's just 
frequently simpler if you do.  You can write a WHERE clause on anything.

In practice, I generally find it easier to do a delete/rebuild than to try and 
track an extra surrogate key.  That is, I'd do something like:

$db->query("DELETE FROM foo WHERE thing_id=5");
foreach ($foo->things as $thing_id) {
  $db->query("INSERT INTO foo (foo_id, thing_id) VALUES (5, $thing_id)");
}

(Actually I wouldn't do that.  I'd use prepared statements because just 
inserting the variable into the string like that is a security risk.  Don't 
do it.  It's just easier to explain without the prepared statement for now.)

That should be fine unless $foo->things is rather large or is very frequently 
edited (for some defintions of large and frequent).

Cheers.

[1] http://en.wikipedia.org/wiki/Surrogate_key

[2]
http://www.bcarter.com/intsurr1.htm
http://r937.com/20020620.html
http://articles.techrepublic.com.com/5100-22-1045050.html

On Saturday 18 August 2007, nitrox . wrote:
> Hi all,
> Is it not considered good practice to have a primary key on a lookup table
> for a database? I have 3 tables setup, games, memberleagues and members.
> The memberleagues table holds the id of the games table and members table.
> The problem I have is that Im not sure how to delete a row from the
> memberleagues table without a primary key. If its not considered bad
> practice I could add a primary key to the memberleagues table and be done.
> Anybody have any tutorials on how to write the php for this?
> _________________________________________________________________
> Messenger Café — open for fun 24/7. Hot games, cool activities served
> daily. Visit now. http://cafemessenger.com?ocid=TXT_TAGLM_AugWLtagline


-- 
Larry Garfield			AIM: LOLG42
larry@xxxxxxxxxxxxxxxx		ICQ: 6817012

"If nature has made any one thing less susceptible than all others of 
exclusive property, it is the action of the thinking power called an idea, 
which an individual may exclusively possess as long as he keeps it to 
himself; but the moment it is divulged, it forces itself into the possession 
of every one, and the receiver cannot dispossess himself of it."  -- Thomas 
Jefferson

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux