Relational database table - id needed?

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Relational database table - id needed?

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

"describes the relationship" meaning it's a many-to-many linking table? It doesn't need an auto_increment.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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).
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

What would a unique ID be for?
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

The second one explains technical keys quite well.
Another name for them is 'surrogate keys'.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

your signature is halarious GM. Thanks... I'll read up on it. :D
Post Reply