Relational database table - id needed?
Moderator: General Moderators
Relational database table - id needed?
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?
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?
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?
Here I assume the following structure of users_properties tableI 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.
Code: Select all
create table users_properties(
user_id int unsigned,
property_id int unsigned,
primary key(user_id, property_id)
);
Code: Select all
create index property_id on users_properties (property_id);
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.
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.
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:
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.
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));
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.