Table / Relationship Design

[JRB]

Hello all,

I've run into this issue before, and am just curious as to your opinion as
to how best to handle.

I have these tables:

tblProjects
ProjectID (autonumber)
ProjectDesc (text)
ProjectLeader (number)

tblStage
StageID (autonumber)
StageDesc (text)

The issue I'm facing is this: I need to create a table which shows
allowable Stages for each Project. Is it best to design the table like
this...

tblProject_Stage
ProjectID (number)
StageID (number)

.... or like this....

tblProjectStage
ProjectStageID (autonumber)
ProjectID (number)
StageID (number)

....based on the fact that I will have other tables that feed off of the
above table (Tasks that are related to project stages, etc.) I could either
design the task table then like this...

tblProject_Tasks
TaskID (autonumber)
ProjectStageID (number)
TaskDesc (text)

.... or like this...

tblProject_Tasks
TaskID (autonumber)
ProjectID (number)
StageID (number)

Any help would be GREATLY appreciated!

Janelle

 

[Duane Hookom]

I like to use a single field as a primary key. I would create the field
"ProjectStageID (autonumber)".

 

[Haris Rashid]

hi Janelle,

Following the database design conventions you should create the junction
table. Your table

tblProject_Stage
ProjectID (number)
StageID (number)

is the junction table. The two fields in this table are the composite
primary key. That is, together they are the key for this table. You should
create teh composite key.

The other tables should just link to the appropriate key and the link will
remain established through the junction table. The juntion table will hold
the allowed stages of the projects. When you need to get all the applicable
stages for a project you can have them retrieved through the junction table.
You don't need the stage key in project table and don't need the project key
in stage table.

When you select from the junction table for any project ID it will return
all the allowed stages.

Regards,

 

[Robert Morley]

I'm going to speak up in disagreement with what the other two have said.

Without question, database normalization guidelines say that you should do
it the way they suggest and create an intermediary table with its own unique
ID relating the StageID and ProjectID.

Having said that, however, it's sometimes more useful/faster/easier to code
your queries if both ProjectID and StageID are readily available in the same
table. For example, if you will frequently be filtering your tasks table by
only ProjectID or only StageID, having them right there in the Tasks table
will save you from having to link in any other tables. The major drawback
to doing it this way is that it's much easier to accidentally get invalid
data in your tasks table by doing it this way. You might, for example,
decide that a certain combination of ProjectID and StageID are no longer
valid...you then have to deal with fixing those entries in your Tasks table,
as well as anywhere else.

So ask yourself how often you'll be wanting to do queries like the above,
and whether the extra time to link through an additional table is a concern
for you...in a table of 100 records, who cares...in a table of a few hundred
thousand, it might be a bigger issue not to have to link.

As I was taught, normalization rules are just guidelines really...they
almost always work out for the best, but there are times when it's better
just to throw them out the window.



Rob

 

[JRB]

Rob,

That's the answer I was looking for. I just want to make sure that however
it is designed, it's easy to manipulate. I will probably be doing a
significant amount of queries based on the information in that table, and I
agree with you that it would be easier to just have the ProjectID and StageID
in the Tasks table. I just see a lot of other people do it the other way
around and wanted to make sure I wasn't missing something important.

Thanks, all of you, for your helpful input!!!!

Janelle

 

[Tim Ferguson]

Without question, database normalization guidelines say that you
should do it the way they suggest and create an intermediary table
with its own unique ID relating the StageID and ProjectID.

This is pretty meaningless. The OP wanted a decision about using a
synthetic PK rather than a natural one formed from the two FKs. "Its own
unique ID" can mean either of these -- what are you actually suggesting?

Having said that, however, it's sometimes more useful/faster/easier to
code your queries if both ProjectID and StageID are readily available
in the same table.

It's mandatory to have both FKs in the same table: else you don't have a
relationship.

it's
much easier to accidentally get invalid data in your tasks table by
doing it this way.

Only if you get the key definitions wrong ..?

You might, for example, decide that a certain
combination of ProjectID and StageID are no longer valid...

In which case you can create a record with that particular combination
and add a column named Invalid and mark it True..

As I was taught, normalization rules are just guidelines really...they
almost always work out for the best, but there are times when it's
better just to throw them out the window.

Nononononono: this comes from a profound misunderstanding of how db
design works. Normalisation is an extremely precise algorithm for
designing robust systems -- it's not a guideline but a ruler. Bend it and
you no longer have a trustworthy structure. R theory is the only way (so
far...) to design db systems that can be _proved_ to be correct: there
are forty + years of maths theory to back up that claim.

Where people disagree about db designs, it's a difference about the
semantics of how the various parts interact: when is an address an entity
in its own right and when it's an attribute of something else, and so on.
Don't confuse that "art" with the "science" of R theory... it's an
amateur mistake and likely to lead you badly astray.

You have not been well served by your teachers.


B Wishes


Tim F