Hey all.

I'm designing a data model for a system I shall be designing.
I have a model and I know it will work, but I was wondering if what I have done is considered to be 'good practice'.

I have a table of users and another table of projects
For each project is assigned a primary, secondary and 'shadow' supervisor.
The way I currently model this relationship is to have all 3 supervisors mapped as the user id of the appropriate person as a foreign key from the users table.

So effectively I have
-----------------------------------------
[Table]User
[PK] User ID
User Name
-----------------------------------------
[Table]Project
[PK]Project ID
Project Description
Project Date
[FK]Primary (Mapped against User ID)
[FK]Secondary (Mapped against User ID)
[FK]Shadow (Mapped against User ID)
-----------------------------------------
Each user can be associated with many projects but can only have one role on that project

Is it considered good practice to do this?
I wouldn't know what affects this 'might' have on the database performance or efficiency. My arguement for this method is that because the user can only have one role on any given project that their is no redundancy in this model.

But i'm pretty new to database design so would glady listen to other people's views

You design is acceptable if the primary/secondary/shadow threesome are "set in stone". It would however require the addition of a check constraint to enforce that "the user can only have one role on any given project", something like:

CHECK (primary != secondary and primary != shadow and secondary != shadow)

A more flexible (i.e. adaptable to future changes) model would be:

[Table]User
[PK] User ID
User Name
-----------------------------------------
[Table]Project
[PK]Project ID
Project Description
Project Date
-----------------------------------------
[Table]Project_User
[PK]Project ID [FK}
[PK]User ID [FK]
Type (check in (Primary,Secondary,Shadow))

This model ensures that each User ony has one role on the Project through the PK constraint, and allows for the addition of a new role type ("Tertiary" perhaps) in the future.

Thanks a lot for your response, it raised an issue i'd nvr thought about (flexibility).
I wouldn't know how to put a 'CHECK' on a database - but this is all handled in code in my application as this is merely a back end to a web applciation i'm writing.

I've never used split Primary Key's in a table before - because i've only ever written a database as a backend to fairly simple web applications i've always just used a bog standard many 2 many relationship.
So your refined model would look somethign like.......
[Table]User
[PK] User ID
User Name
-----------------------------------------
[Table]Project
[PK]Project ID
Project Description
Project Date
-----------------------------------------
[Table]Project_User
[PK]LineID
Project ID [FK]
User ID [FK]
Type (check in (Primary,Secondary,Shadow))

Using that model i've always used application code to check the constraints the data

you would not need LineID, and in fact, if you did use it as the primary key, you would need an additional UNIQUE constraint on the pair of columns ProjectID and UserID to ensure that a given user can be linked to a given project at most once

that's one of the benefits of having them as a compound primary key, the primary key is unique by definition

also, the CHECK constraint on the Type column is usually handled by application logic anyway, e.g. from a dropdown list, the CHECK constraint ensures that you can't enter an invalid Type through SQL

many databases don't support CHECK constraints

(you didn't say which one you're using, or if you did, i missed it)

Wrong perspective: your web application is merely the (current) front end to the database you are designing.

Thanks for your responses - i've got my model sorted now, and i've also used composite keys through the rest of my database where applcable to refine it further.

Just to let you know the weapon of choice for my project was SQL Server 2000 don't know if that's capable of 'CHECK' or not.

yes, sql server supports CHECK constraints

Hi,
I can assure you.... as the business will change, your design will break.
The problem is your business entity is a 'role'. Let me explain like this. A 'role' is a party or responsibility some plays or has. EVERY SINGLE database you ever design that has any roles, should be designed so that you have -SEPARATELY - a person, and the roles they play.

The reality of ANY BUSINESS is that one single person can and will over time play more than one role. This assures, that no matter what new 'role' the business asks for tomorrow is already built into your design. AND THEY WILL, trust me.

Many database designers just don't get this simple concept.

But if you ask yourself this question: Can a 'primary' supervisor ever become a 'secondary' supervisor? .... or can a seconadry supervisor ever become a shadow supervisor, or.. etc. OF COURSE THEY CAN. A person can and will (sometimes over time, sometimes at the same time) play many roles.

OK...sorry. I'm just trying to help see a basic concept that save you years of re-work when the next role comes along.

So create a separate entity for a 'person', and a separate entity for 'roles'. Then when you related a person to any other business entity, you drag their id, and their role id together.

Vmusic

I will keep that in mind
Thanks for your input Vmusic

Vmusic,

Can you please talk louder? I can't hear you.

Ravi

But I disagree with this though because no manager is going to assume the position of primary AND secondary on the same project - because the nature of the job is that if primary is unavailable then secondary will take their place.

But this is something you would not have understood as you don't have the specifics of the project like I do.

Thanks for your input anyway, I understand what it is you are trying to say:
Why build a system that isn't flexible to move with the business changes and development?

And I agree

Thanks