Have Geek Brain

Is a primary key needed in this particular scenario?

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 have similar tables of the second kind, like the phone table (id_fk, phone_no) where id_fk is a foreign key referencing an identifier 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 another scheme?

PS: Tables are for storing contacts by the application

0


a source to share


8 answers


You are almost always better off using the primary key. Even if you think you don’t need it now, as your database grows and becomes more complex, you will quickly run into problems without it.



Personally, I also recommend using a data agnostic column for your primary key like guid / uuid / serial. By using a field that does not contain usable data, you will never run into a situation where you need to update the primary key, which could be another messy operation when your database grows.

+1


a source


In the cases you described, the primary key is the whole table (id_fk, phone_no), since the rows are not equally split, are an unordered collection, and only have a value and not any identity of their own.



+4


a source


I would add a simple auto-incrementing "id" primary key for each of these tables, it cost very little and makes it easy to reference specific rows later.

It would also be a good idea to look at a different naming scheme for your foreign keys, "id_fk" can be a little confusing as it doesn't give an indication of which table "id" it is referring to, something like "name_id" "might be better choice.

+2


a source


My design philosophy is that each table always has one int id column.

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

+2


a source


There is a primary key for the telephone table. Each pair (id_fk, phone_no) uniquely identifies each record, so the primary key in this case is composite.

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

+1


a source


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

Personally, I prefer to use strictly technical primary keys, which usually means an auto-numbered column. One of the advantages is that it is easier to update and delete records using the ID, rather than remembering the old value in HTML formats etc.

+1


a source


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

+1


a source


Why do you need separate tables for email addresses and phone numbers? Keeping them all in one table is much more efficient. Your schematic might 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

);
0


a source






More articles:


All Articles