Does it make sense to create new table or add fields

Question

I apologize if this is just another way of asking about storage space on a MySQL table such as these: NULL in MySQL (Performance & Storage)

but my question really pertains to best practice.

The Problem

Let's assume I have a table which collects all 'necessary' information from a customer at a given point in time. 12 months down the road, I realize I would also like to capture the person's date of birth and gender if they wish to supply it (the number of fields is somewhat irrelevant but understand it could be one or 50 additional fields).

Is it best practice to add the new fields into my existing table, setting and allowing all initial values to be NULL, then updating existing records with the new data

OR

create a new table(s) and establish a relationship with the primary table based on the presence of the primary key?

Answer 1

I am not an expert here, so this might not be a good answer. Just wanted to put one point in perspective:

There could be one downside of adding columns to existing table : If you are using an ORM (e.g. hibernate), it will fetch all the columns when you run a select query. So if the number of rows become larger there's more data to fetch so query may become slower. If the column is not used frequently it might make sense to create separate table?

Answer 2

I suggest adding new tables.

There is no concept of null in the relational model. Using the Closed World Assumption, model missing information by its absense from a relation (and hence the database). SQL's three value logic is unintuitive and leads to frequent bugs (plenty of evidence of this on Stackoverflow!) Therefore, do not seek to introduce nullable values into your SQL tables. Normalizing to the highest normal form (6NF) would result in your new attributes being in separate tables.

Also, it sounds like you may need multiple new tables e.g. one for date of birth, another for person did not wish to supply date of birth and another for person has not been asked to supply their date of birth (perhaps the latter can be omitted and instead inferred from the other two, Closed World Assumption again).

Minor point: gender = feminine, masculine, etc, sex = female, male, etc.

Answer 3

a rule of thumb i use is that if i feel this data is an extension to this customer record that will be rarley used, i use extension table and save the space. if it's a un-detachable part of your customer record i would go for one table with nulls.

it's not just the space that plays a part here but rather your data modeling for the specific product. the modeling help you understand and further design your application as it grows and changes.

Hope it helped.

Answer 4

Don't worry so much about when the columns are added to the table, whether it's today or 12 months later. Instead, just think about the relationships involved. There's a 1:1 relationship between a person and their gender and birthday, so it makes sense to keep these attributes in the existing table rather than creating a new one.

Answer 5

In my opinion, it would be best to add new columns to existing table, because it would be much easier to maintain it later on the road. Also, it logically belongs in the same table.