Design issue 1

[logidude]

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
-Contract
-ContractType

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.

 

[johnherman]

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

 

[logidude]

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)