Do I need to define a new primary key field for each table?

Question

Hi,

I have a few database tables that really only require a unique id that references another table e.g.

Customer      Holiday
********      *******
ID (PK)  ---> CustomerID (PK)
Forename      From
Surname       To
....

These tables such as Holiday, only really exist to hold information regarding a Customer. Therefore, do I need to specify a separate field to hold the ID for the holiday? i.e.

Holiday
*******
ID (PK)
CustomerID (FK)
...

Or would I be ok, in this instance, to just set the CustomerID as the primary key in the table?

Regards, James.

Answer 1

This really depends on what you are doing.

if each customer can have only 1 holiday, then yes, you could make the customerid the primary key.

If each customer can have multiple holidays, then no, you would want to add a new id column, make it the primary. This allows you to select holidays by each customer AND to select individual records by their unique id.

Additionally if each customer can only have 1 holiday, I'd just add the holiday information to the table, as a one-to-one relationship is typically un-necessary.

Answer 2

If I understand your question correctly, you could only use the Customer table as a primary key in Holiday if there will never be any other holiday for that customer in the table. In other words, two holidays for one customer breaks using the Customer id as a primary key.

Answer 3

If there will ever be an object-oriented program associated with this database, each entity (each row) must have a unique key.

Your second design assures that each instance of Holiday can be uniquely identified and processed by an OO application using a simple Object-Relational Mapping.

Generally, it's best to assure that every entity in the database has a unique, immutable, system-assigned ("surrogate") key. Other "natural" keys can have unique indexes, constraints, etc., to fit the business logic.

Answer 4

Previous answer correct, but also remember, you could have 2 seperate primary keys in each table, and the "holiday" table would have the foreign key to CustomerId.

Then you could manage the assignment of holidays to customers in your code, to make sure that only one holiday can be assigned to a customer, but this brings in the problem concurrency, being 2 people adding a holiday to a customer at the same time will most probably result in a customer having 2 holidays.

You could even place holiday fields in the customer table if a customer can only be created with a holiday, but this design is messy, and not really advised

So once again, option in your question 2 still the best way to go, just giving you your options.

Answer 5

In practice I've found that every table should have a unique primary key identifying the records in those tables. All relationships with other tables should be explicitly declared.

This helps others understand the relationships better, especially if they use a tool to reverse-engineer the schema into a visual representation.

In addition, it gives you more flexibility to expand your solution in the future. You may only have one holiday per customer now, but this is much more difficult to change if you make customer ID the primary key.

If you want to mandate the uniqueness of customer in the holiday table, create a unique index on that foreign key. In fact, this could improve performance when querying on customer ID (although I'm guessing you won't see enough records to notice this improvement).