Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Database Design - Creating a table with only two values, or make it a column that has either of those two values

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
like image 592
chadwtaylor Avatar asked Dec 28 '22 02:12

chadwtaylor


2 Answers

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.

like image 56
Habib Avatar answered Jan 13 '23 12:01

Habib


I would use the latter, no question about it.

  1. Can you be absolutely sure there will NEVER be another royalty type? No, you cannot.
  2. You can validate the royalty_type_id column by a foreign key relationship...not so with royalty_type...you'd have to use a check constraint with the text values hard-coded.
  3. With the royalty_types table, you can add new features as needed to the royalty type entity itself.
like image 39
JeffSahol Avatar answered Jan 13 '23 12:01

JeffSahol