Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Design issue

Design issue

Design issue

Hi everyone,

I'm currently building a "small" web-application ... it's small compared to what's out there, but would be my first project of this scale.

I'm familiar with ER Diagrams and most of the lingo however I'm not sure if I have the right model at the moment.

I have the following tables:

Customer have a n:m relationship to a Contract table.

Contract have a 1:n identifying relationship to a ContractType table.

Now my problem is with the contracts, depending on the type I will store different informations.

Say ContractType_1 (A la carte) will store a nbOfHours and costPerHours, etc.

ContractType_2 will store a Project with an associated cost, etc.

I was gonna do two tables, one for each type of table and have a n:m relationship for each between them and contract.

Second option would be larger table with a hell of a lot of NULLs depending on the contract type...which sounds wrong to me.

Third, ditch the contract model all together and have each ContractType as a primary table with a n:m to Customer

Am I just missing the big picture here?

Thanks in advance, I hope the post was clear.  If I left out any required information don't hesitate to ask for precision.

RE: Design issue

I think you need a master Contract Table with the basic info that applies to all contracts, such as start/end dates, and perhaps terms, keys to attachments, addendums, etc.  The Contract Table would then have one or more detail tables (subtypes of the Contract supertype using E-R terms), each of which would support the appropriate Contract Type(s).  This is the 3NF (3rd Normal Form) relational design method as used in OLTP (Transaction Systems).  In dimensional modeling, it all might be collapesed into one Contract table with a lot of nulls as that design supports rapid querying (Decision Support/DW systems).  

The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

RE: Design issue

Hi johnerman,

Thank you very much for the reply, from there I should be able to rework my model a bit I'll definitely go for 3NF first.  

I'll then de-normalize as needed for performance (I doubt that will be needed considering the small amount of data)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close