Currently we have a table with 35 fields in it.
Now, we got a requirement to add 5 extra columns but these columns will not be fill always.
So, I was thinking just add one column and have that id as the foreign in another table with 5 or less rows depends on the availability of data.
Which is better
- add the column ?
- add a new table ?
Best Answer
The decision does not depend on the number of columns you already have, but on whether those 5 columns do belong to what you are trying to model.
There is no rule that says: "if you have 10 columns, keep in same table; use a separate table when you reach 36 columns".
If those 5 columns are like a person's street, town, region, country, then no; they should be on a separate table "location" or similar. If those 5 columns are like a person's 1st name, last name, 2nd last name, ID-number, and email address, then they should probably be there.
However, if your table already has 35 columns, then you probably haven't done much normalization to begin with and it looks like you're just stuffing everything on that table...
Update
Assumptions:
moving from a to b there is a number of gps data which is kept in this main table
, I'm not sure I understand what info is stored, or why it is stored in the table called association / journey instead of in some associative table (given that there seems to be "a number of gps data" and not just "one gps datum per journey")Given this, I would add your 5 columns to the RFIDStatus table, since
This 5 new column are indicating the status of the rfid devices
. Below is a first attempt at modeling something similar to what you might be modeling...However, as I mentioned in the comments, it all depends on what you're modeling. Please update your question with more details so other people can give you a better answer. Also, you should read something about database modeling so you start from a good basis and a good understanding of what's needed when modeling a database; it will also help you to ask better questions regarding database design.