Primary key question

[harkly]

I have about 24 tables and all of them will have the same userID which connects them. What I would like some advice on is should I have another unique identifer other than the userID, I currently am doing that?

 

This is what I have them right now

 

CREATE TABLE user (userID VARCHAR(32) NOT NULL PRIMARY KEY,
pswd VARCHAR(32),
email VARCHAR(50),
                ...
CREATE TABLE background (backgroundID SMALLINT(7) NOT NULL PRIMARY KEY AUTO_INCREMENT,
userID VARCHAR(32) NOT NULL,
religion TINYINT(1),
                ...

CREATE TABLE appearance (appearanceID SMALLINT(7) NOT NULL PRIMARY KEY AUTO_INCREMENT,
userID VARCHAR(32) NOT NULL,
height SMALLINT(6),
                ...

 

But should I do this instead???

CREATE TABLE user (userID VARCHAR(32) NOT NULL PRIMARY KEY,
pswd VARCHAR(32),
email VARCHAR(50),
                ...
CREATE TABLE background (userID VARCHAR(32) NOT NULL  PRIMARY KEY AUTO_INCREMENT,
religion TINYINT(1),
                ...

CREATE TABLE appearance (userID VARCHAR(32) NOT NULL PRIMARY KEY AUTO_INCREMENT,
height SMALLINT(6),
                ...

 

 

[rubenski]

What I understand from your post is that you want to store Users and their appearance and religion. The appearance properties are unique to a user and should therefore be stored in the user table.

 

The question you should ask is whether a lot of users will share the exact same appearance. If the answer is no (which it is), don't split it of into a separate table. Appearance properties are very unique to a specific user.

 

Religion on the other hand is a property that is shared by many users and therefore qualifies for a separate table.

 

Here is what I would do:

 

Table User

- userID (primary key, autoincrement)

- pswd

- email

- height

- weight

- hair_color

- another_user_property

- religionID

 

 

Table religion

- religionID (primary key, autoincrement)

- name

- number_of_followers

- is_monotheistic

- another_religion_property

 

The religionID in the user table is a reference to a row in the religion table.