DB relations

Hi,

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.

[color="#0000FF"]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 ?[/color]

… 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

8)

Hi Raoul,

I’m often using the latter to avoid huge composite keys or because of unsufficient key candidates. These keys are called ‘surrogate keys’.

The most important is that surrogate keys make your life easier :wink: If you think in dimensions like real data-warehousing they are indispensable.

The only thing you have to keep in mind is that it’s not allowed to have the same natural keys with different surrogate keys. E.g. the same task-key and employee-key with different ids (if lets say you add a autoincrement field as the surrogate key). Otherwise you would violate the second normal form (2NF).

It also makes it easier to change/extend your schema. For example you have build the database and applications and after some time the COMPANY is splitted into different locations and a TASK needs to be identified by TASK, EMPLOYEE and LOCATION (or s.th. like this). Using surrogate keys you only need to add an attribute while you would need to change every single point in your application where you’re using this relation when using a composite key.

Just my 2 cents :)

Regards

hi yoshi,

and thanks for this very clear reply !

Since I post this message I’ve been doing some search and learnt that I’ve been using surrogate keys without even knowing it ;)

…db modelisation is a vast subject and I have a lot of theroy to learn.

Thanks again

B)