8

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?

Community
  • 1
  • 1
JM4
  • 6,740
  • 18
  • 77
  • 125

5 Answers5

15

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.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • 1
    +1 Joe makes a good point - a 1:1 relationship means that the columns are a part of the original table, so add them there instead of creating a new table. – Raj More Sep 08 '11 at 18:36
  • Thanks @raj more, See my comment below tal's post below. I am really thinking about it from an 'upgrade' perspective though I feel the circumstances are similar/identical. – JM4 Sep 08 '11 at 18:37
  • 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 rows on select query. So if the number of rows become larger the query becomes slower. If the column is not used frequently it might make sense to create separate table. – user18853 Jul 03 '16 at 17:58
  • I second what @user18853 said. Another reason: if you're using different modules which make use of a table, but not all your users will have those modules, despite being a 1:1 relationship, there's no point on cluttering a table with zillions of fields which are needless for many of those users. I'd rather separate them in a specialized table. – luis.ap.uyen Feb 11 '21 at 11:52
3

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.

Madara's Ghost
  • 172,118
  • 50
  • 264
  • 308
  • thanks for the advice. When you say 'easier to maintain', can you expand on that a bit? Do you mean simply from a programming perspective (fetch from one insert into another) type thing? – JM4 Sep 08 '11 at 18:21
  • For example, you dont have to keep a join to get all the data OR you forrgot to join in one of tjhose table then the problem of incosistency/two queries to keep consistent – manocha_ak Feb 28 '13 at 04:21
0

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?

user18853
  • 2,771
  • 1
  • 21
  • 17
0

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.

Tal
  • 154
  • 1
  • 4
  • I am thinking about it from the perspective of 'user A' which is a basic user, that user can 'upgrade' their content but to do so have to provide additional information. Therefore, a 'type' field would also exist so as opposed to joining tables based on the field value, I would run the same query in one table. Make sense? – JM4 Sep 08 '11 at 18:35
  • from the data perspective i would make an extension table (something like 'user_type_b_ext') that you can eitehr left join all the time by id or join based on user type if you need it (i mean if you need it besides for knowing there;s extra data). i wrote recently an application where different user types actually had little in common, so i has users table and two extension tables called consumers and businesses which held totaly different data for each type. – Tal Sep 11 '11 at 06:10
0

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.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138