This is a question about how to create an efficient db schema using the appropriate relations between tables, and I am a newby on DB modelisation (as you'll see you you are brave enough to read this message to its end )
Let's say I have three tables COMPANY, EMPLOYEE, TASK and the relation between these 3 tables are :
- A company has 0 or n employees and an employee belongs to one and only one company
- an employee has 0 or n tasks and a task is assigned to one and only one employee
So, we have a 1-n relation between COMPANY and EMPLOYEE, and another 1-n relation between EMPLOYEE and TASK.
To create the schema, we have to choose between Identifying and Non-Identifying relations type. In this example, we said that an employee with no company is not allowed, nor a task with no employe, so we will choose an identifying relationship type.
If you see any big mistake up to now, please tell me, because it's going to become a little bit more complicated (not too much)....
Ok, so I've created the schema, and each time I've added an Identifying relation between 2 tables, the primary key has been copied from the '1' end to the 'n' end. The problem is that the TASK table has a composite key, and if I add another table like SUB_TASK, it will inherit all parents key and end up with a composite key made of 4 columns ... etc...
To avoid this, I thought about replacing all inherited key by NOT NULL foreign keys and keep the id as the only primary key : bye bye composite primary key !!
..but is it a good thing to do that ? That's my question.
To summarize : what is the difference between foreign keys being part of a composite primary key, and NOT NULL foreign keys + single auto increment primary key ?
... now if you've reached this point of the message (first, congratulation !) I hope you'll be able to reply (if you have a good reference site for DB modelisation, I'll take it too).
Thanks in advance