Forum Moderators: open
This is an online registration application to allow people to register and pay for any one of a set of (different types of) programs available. All of the programs have a set of common fields that the registrant has to enter.
Few (very few) programs have a varying set of optional fields (some of which are mandatory).
ie. Some types of programs need fields zzz and yyy. Others need aaa and bbb fields.
Right now, I just have the common set of fields in a single Table. I also have a seperate transaction related table (if that is of any importance).
So my question is, do I create a more 'normalised' database and put these additional fields in a seperate table, or do I just add these additional fields in the main registration table itself and use it only for those that need them?
And if I do use a separate table, how is that to be structured?
I'm sure this little 'problem' has come up before and someone has the perfect solution for this. I just need to know what that is :)
ps. If it is needed, I could post the table structure as I have it.
There's normalization and there's abnormalization. Generally speaking, you need another table as it sounds like you'll have many many null rows there, but sanity is required here, so look at your overall application and decide which works best in the long-term. One important question is whether this situation will come up again with more fields. If so, then another table is more likely in order.
Hope this helps.
Bill
Programs (ProgID, Name etc)
Options (optionID, Name etc)
ProgramOptions (ProgOptID, isMandatory, OptionID, ProgID, Cost)
This way the same option can be mandatory in some, optional in others and have different costs depending on the program, giving some future-proofing if you need it.
In orders, just define the max number of options allowed and have a field for each store the ProgOpt ID or null value / not used flag.
Orders (ProgID, ProgOptID1, ProgOptID2, etc)
Or if appropriate create an invoice line for each option:
OrderLines (ProgID, ProgOptID)
HTH
