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
