It is more of a database design question and am curious how professional database admins would approach when there are only two set of values - take an example below of two design scenario and which you would choose; and why?
Scenario One
table INVOICES
-------------------------------
id | royalty_fee | royalty_type
-------------------------------
1 | 15 | percentage
2 | 10.00 | fixed
1 | 25 | percentage
1 | 25.00 | fixed
or....
Scenario Two
table INVOICES
----------------------------------
id | royalty_fee | royalty_type_id
----------------------------------
1 | 15 | 1
2 | 10.00 | 2
1 | 25 | 1
1 | 25.00 | 2
table ROYALTY_TYPES
-------------------------------
id | label
-------------------------------
1 | percentage
2 | fixed
Approach 2 is better. Your developers can use the 2nd table ROYALTY_TYPES for lookup, also if you want to add a new type or edit/update the label of the ROYALTY_TYPES you can do it easily.
If you edit/modify label in ROYALTY_TYPES it will require change in only one place and the table INVOICES will be unaffected
EDIT:
I usually look for three things with respect to database design:
How easy it is to Add: In your first approach, you will not able to add new Royalty types if you don't happen to have a record for invoice. I mean if you want to add a new type lets say HalfFixed for future invoices. You will not be able to do it, without getting an invoice of of type HalfFixed
How easy it is to update Lets say if you want to update Royalty Type Fixed to 'FIX'. If you are following first approach then you would be required to update it in multiple rows.
How easy it is to delete Now if you want to delete a type from Royalty Types then according to your first approach you will have to run an update statement on the table and setting the particular royalty type to NULL.
I would use the latter, no question about it.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With