Is a primary key required in this particular scenario?

Question

I have a name table with (id,first_name,middle_name,last_name,sex) and an email table with (id_fk,email_add)

Infact I will be having similar tables of the second kind, like a phone table (id_fk,phone_no), where id_fk is the foreign key referring to the id in the name table.

Is it required or rather is there a good reason to have a primary key in the second and third tables? Or other similar tables? Or would you suggest a different schema?

PS: The tables are for a contact storing app

Answer 1

In the cases you describe, the primary key is the whole table (id_fk, phone_no) since the rows are not otherwise shared, represent an unordered collection and have only value rather than any sort of identity of their own.

Answer 2

There is a primary key for the phone table. Each (id_fk, phone_no) pair uniquely defines every entry so, in this case, the primary key is a composite one.

http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx

Answer 3

You are almost always better off using a primary key. Even if you think it's not needed now as your database grows and gets more complicated you will quickly run into issues without one.

Personally I'd also recommend using a data agnostic column for your primary key something such as a guid/uuid/serial. By using a field that does not contain usable data you will never be in a situation where you have to update the primary key which can be another messy operation once your database grows.

Answer 4

You could make (foreign key, phone number) a composite primary key.

Personally I favour the use of strictly technical primary keys, typically meaning an auto number ID column. One of the advantages is it'll make it easier to update and delete records by using the ID rather than remembering the old value in HTML forms and so on.

Answer 5

I'd add a simple auto-incrementing "id" primary key to each of those tables, it costs very little and makes it easier to reference specific rows later.

It might also be a good idea to look at a different naming scheme for your foreign keys, "id_fk" could be a bit confusing as it provides no indication of which table's "id" it is referring to, something like "name_id" might be a better choice.

Answer 6

You should definitely have a primary key for all of your tables. In the case of the (id_fk, phone_no) you could either use a composite key consisting of both columns, or you could add another column to be the primary key. I would recommend the latter as it will ultimately reduce the complexity of everything and will be much more accommodating if you are using an ORM.

Answer 7

Why do you need separate tables for email addresses and telephone numbers? Storing them all in the same table is much more efficient. Your schema could look like this:

(Pseudo SQL)

Create table ContactName (
       CONTACT_ID integer not null default auto_increment,
       etc
       )

CREATE TABLE TELECOM_CLASSES (
    CLASS_ID INTEGER NOT NULL DEFAULT AUTO_INCREMENT,
    CLASS_NAME VARCHAR(200) NOT NULL 
              CHECK (CLASS_NAME  IN ('email','tel','fax',etc)),
    etc
     );

CREATE TABLE CONTACT_TELECOMS (
    TELECOM_ID INTEGER NOT NULL DEFAULT AUTO_INCREMENT,
    CONTACT_ID INTEGER NOT NULL REFERENCES CONTACTNAME (CONTACT_ID),
    CLASS_ID INTEGER NOT NULL REFERENCES TELECOMS_CLASSES (CLASS_ID),
    TELECOM VARCHAR(200) NOT NULL,
    etc

);

Answer 8

My design philosophy is that every table always has a single int identity pk column.

This seems to be a controversial decision, as previous comments on SO have shown, but it's also very convenient.