Page 1 of 1

Relational database table - id needed?

Posted: Mon Jul 10, 2006 5:23 pm
by Luke
I have a table called users and a table called properties. Then I have a table called users_properties that describes the relationship between the two. Does this third table need an auto_incremented id field?

Posted: Mon Jul 10, 2006 5:25 pm
by feyd
"describes the relationship" meaning it's a many-to-many linking table? It doesn't need an auto_increment.

Posted: Mon Jul 10, 2006 5:35 pm
by Luke
Well... it's actually a one-to-many relationship, but i did not design the database;

I am adding a users table to the database (which can have many properties, but properties can only have one user). In order to not have to rewrite a bunch of code, I'm just adding a third table like you would in a many-to-many. None-the-less you have answered my question.

Does a table need to have an index? Phpmyadmin whines about there being no index when I don't have one, but there are no problems with it right?

Posted: Mon Jul 10, 2006 5:40 pm
by Benjamin
Why are you using a third table?

Why not..

user_table
user_id auto_increment

Then insert a record, get the mysql_insert_id()

user_properties
user_id <- insert mysql_insert_id()

Then the tables are linked by a unique user_id

Wouldn't that be better?

Posted: Mon Jul 10, 2006 5:46 pm
by Luke
It would if not for the fact that the sql statements (put in place by some sloppy coder before me) are all short-hand and the site would explode if I added a user_id column to the properties table. This job needs to go out very soon... so I don't have time to try and fix sloppy code.

Posted: Mon Jul 10, 2006 6:30 pm
by Weirdan
Does a table need to have an index? Phpmyadmin whines about there being no index when I don't have one, but there are no problems with it right?
Perhaps even two of them, depending on the nature of your queries. One for user_id column and one for propery_id column.

Posted: Mon Jul 10, 2006 6:51 pm
by Luke
Weirdan wrote:
Does a table need to have an index? Phpmyadmin whines about there being no index when I don't have one, but there are no problems with it right?
Perhaps even two of them, depending on the nature of your queries. One for user_id column and one for propery_id column.
can you elaborate?

Posted: Mon Jul 10, 2006 8:31 pm
by Weirdan
I have a table called users and a table called properties. Then I have a table called users_properties that describes the relationship between the two.
Here I assume the following structure of users_properties table

Code: Select all

create table users_properties(
   user_id int unsigned, 
   property_id int unsigned,
   primary key(user_id, property_id)
);
From my experience this type of tables usually require additional key:

Code: Select all

create index property_id on users_properties (property_id);
Thus we have two keys (one primary and one additional).

Posted: Tue Jul 11, 2006 4:12 am
by GM
I agree with Weirdan - this is the solution I would use.

One table for Users, keyed on user_id
One table for properties keyed on property_id

Given that one user can have many properties, your user-property table needs both user_id and property_id as a key (just user_id would mean 1 user => 1 property, just property_id would mean 1 property => 1 user) so the solution is a composite primary key for both columns.

Posted: Tue Jul 11, 2006 10:03 am
by Luke
What would a unique ID be for?

Posted: Tue Jul 11, 2006 10:28 am
by GM
I'm not sure of the exact correct technical answer, but I always reason like this:

A unique ID is used when you don't really have any other way of uniquely identifying a row, and where the id assigned to the row is completely irrelevant to everything except the internal working of the program.

For example, a "Categories" table should look something like this:

Code: Select all

CREATE TABLE category (
id_category INT(3) unsigned not null auto_increment,
de_category VARCHAR(50) not null,
PRIMARY KEY (id_category));
Here, without the unique id, you've got no way of making a row in the table unique, and also, who cares if the "Fruit" category has id number 2 or 309? It is enough that it is unique.

Same thing with a "Post ID" in a forum database: It must be unique, and there really is no other way to identify a single row in the table. Also, it doesn't matter what id you give the post, as long as it's unique.

I found these by googling "Technical Key": http://www.aisintl.com/case/technical_keys.html and http://www.tonymarston.net/php-mysql/da ... .html#keys

The second one explains technical keys quite well.

Posted: Tue Jul 11, 2006 10:36 am
by Weirdan
The second one explains technical keys quite well.
Another name for them is 'surrogate keys'.

Posted: Tue Jul 11, 2006 10:36 am
by Luke
your signature is halarious GM. Thanks... I'll read up on it. :D