Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?
share|improve this question
add comment

4 Answers

Even 35 seems a bit too much to me unless you implement data warehouse and trade normalization for performance. Without having all details it's hard to give a good advice, but in general I'd prefer 1 to 0..1 relationship then having columns with NULL

share|improve this answer
    
@alex actually this table receives data from gps devices which are comma delimited. The number of data when broke down is 26 altogether and the rest of the 7 more fields are id which are foreign key of different tables and not all are filled depends on the event message represented by the string. So that is why we having problem with normalisation. So is it ok not to normalize in this case? –  newbie14 Jun 15 '12 at 13:52
    
@newbie14 : If you deal with dynamic number of attributes, maybe entity-attribute-value pattern will match your needs better. Normalization itself , in my opinion, doesn't have to be an ultimate goal, but it's always helpful to try normalizing model first, and if it requires too many efforts, give up a bit of normalization in favor of clearness and consistency. Another point which is worth attention, is whether your application really cares about all 26 attributes? If some of them have zero value, there is absolutely no need in storing them in separate columns. BLOB/TEXT/XML can be used –  a1ex07 Jun 15 '12 at 15:45
    
Clarification : by "0 value " I meant no business value... –  a1ex07 Jun 15 '12 at 15:47
    
@alex most of it have values such the latitude,longitude,speed,date time,device type,device serial etc. So most of it will be use. The remaining 7 fields actually foreign keys to other tables itself. So what is your opinion am I ok on the right track or need some modifications. –  newbie14 Jun 16 '12 at 3:24
add comment

Before I give my answer, I'd like to say this: If you have a table with 35 columns, you should think about normalizing the table. That way, you would not have to worry about addin more columns.

With regard to our original question, you would like to add 5 columns.

Since you mentioned that not 5 columns would be filled, you have to decide what kind of queries you are willing to run.

If you use a second table, you would have to make it have the same primary key as the original table. If you ever need columns from both the first and second tables, you either do two queries or an INNER JOIN of the tow tables. Why introduce the additional queries?

Will any of the 5 new columns be indexed? If yes, you are better off adding the 5 new columns to the table. Then, any indexing of columns from the old and new set of columns would be more simplified.

Therefore, I say again: Think of normalizing the table from 35 or 40 columns. If you cannot do that, just add the 5 news columns to the table. It will make for cleaner queries in the long run.

share|improve this answer
1  
Or they could use a view? –  jcolebrand Jun 14 '12 at 17:27
2  
@jcolebrand A view would just hide the gory details of messy SQL under the hood. Once such views are written, it would appear to be smooth sailing from there until it is time to purge old data from the table, or should I say tables (as long as newbie is OK with that). –  RolandoMySQLDBA Jun 14 '12 at 17:34
    
@rolando actually this table receives data from gps devices which are comma delimited. The number of data when broke down is 26 altogether and the rest of the 7 more fields are id which are foreign key of different tables and not all are filled depends on the event message represented by the string. So that is why we having problem with normalisation. So is it ok not to normalize in this case? –  newbie14 Jun 15 '12 at 13:51
add comment

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

first we have a table called association then based on that table we have the gps data linked to the assocition. In addition we also got alert tables linked to the association too. So association is the main table here. Each device also is table by itself but it will be linked to a association when and where it is being used.

Basically the association is just like a trip from a to b. So moving from a to b there is a number of gps data which is kept in this main table [?]. If in this trip there are alerts then is kept in the alert table. This 5 new column are indicating the status of the rfid devices which are linked to the main gps device. But in a trip not necessary to have 5 it can be less or even none too.

Assumptions:

  • For each trip there can be 0..N alerts
  • as I understand it, what you call association is the description of a journey
  • multiple RFID devices are used per journey or trip.
  • A GPS Device has 0..N RFID devices associated to it; in other words, each rfid device is associated to one GPS device (not sure I understand the logic of this, though; why are RFID devices used? what's the role of the GPS if you are )
  • Given that you are using a GPS device, I assume there will be multiple readings during a Journey, in combination with RFID readings (?)
  • When you say 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...

[     Journey    ]<——————[  Alert  ]
| fk_from        |       | fk_trip |
| fk_to          |
|                |—(from)—>[    Location    ]
|                |–—(to)—–>| long/lat/name… |
|                |
|                |<—————[    RFIDStatus    ]—————>[RFIDDevice]—————>[ GPSDevice ]
|                |      | fk_rfiddevice    |      | fk_gps   |      |           |
|                |      | fk_journey       |                        |           |
|                |      | datetime         |                        |           |
|                |      | *status details* |                        |           |
|                |<———————————————————————————[ GPSStatus  ]———————>|           |
|                |                            | fk_journey |
|                |                            | fk_gpsdev  |
|                |                            | datetime   |
|                |                            | latitude   |
|                |                            | longitude  |

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.

share|improve this answer
    
I afraid if I do cut them into two table then I have to do heavy join because even now for the 7 foreign keys at times I have to do 5 joins so that is already quite heavy right? –  newbie14 Jul 2 '12 at 14:53
1  
Not necessarily. If the indexes and queries are properly set up, and if the tables you are joining don't have millions of rows each, it doesn't need to be heavy. You should always start with a normalized database, and denormalize only when needed, where needed, making very conscious decisions on why you are denormalizing each specific part of the database. However, it might be better not to touch now that table with 35 columns, given the dependencies you may have all over. But take a good start with those 5 new columns. –  Rafa Jul 3 '12 at 8:31
    
this table will eventually be having million of rows is for sure. So you idea is to have separate table for the 5 new columns right? –  newbie14 Jul 4 '12 at 5:15
1  
No. What I'm saying is that it depends. Put the columns where they belong (probably another table, but it may also be two other tables). You haven't described what you're trying to model; therefore it's impossible to make a decision of where the columns should be. Please read on database modeling & design... –  Rafa Jul 4 '12 at 12:06
    
ok what I am trying to model is first we have a table called association then based on that table we have the gps data linked to the assocition. In addition we also got alert tables linked to the association too. So association is the main table here. Each device also is table by itself but it will be linked to a association when and where it is being used. –  newbie14 Jul 5 '12 at 15:15
show 3 more comments

Taking this question in a vacuum, there are many factors to consider:

  • How large is the table? (Adding new columns could incur a tremendous amount of I/O. This could impact availability not only of this system, but also of any other system or process using the same storage subsystem.)

  • How will each method impact total storage space (related to the previous point)? (Adding columns to the existing table may or may not use less space than creating a new table, depending on the design and how frequently the new fields are stored.)

  • What are the access patterns of queries on this table? If you're in a DW scenario, scan speed is critical, and adding more columns to a fact table will likely slow it down.

  • Will this change affect network availability? (Is the database replicated in some way?)

  • Are the new columns going to be used frequently, or only occasionally? (Vertical partitioning?)

  • Does the DBMS you're using support online operations? (Would this change mean downtime?)

  • Is the existing design fully normalized? Other answers have touched on this; IMO, tables with 35 fields are suspect and would draw my attention, but that doesn't necessarily mean the design is incorrect. For your specific scenario, however, you said (referring to the existing design), "@alex most of it have values such the latitude,longitude,speed,date time,device type,device serial etc." -- the existence of both device type and device serial number (and possibly latitude and longitude as well) in all rows indicates to me that there are indeed opportunities for normalization.

  • Would putting the new columns in a separate table impact performance too much? (I say "too much" because it will impact performance because of adding a join, but will performance still be at a level that's acceptable to the business?)

  • Is there a need to add more columns in the future? (Try to future-proof the design if you can.)

  • How much will application/business logic need to be changed? Do applications have direct table access? Sometimes this can be a significant impediment, particularly for a normalization project.

I'm sure this is by no means an exhaustive list, but you get the point. You have to decide the best strategy for your environment by considering all the factors, some of which I've enumerated above.

share|improve this answer
    
I appreciate your long list of information. So as I have listed earlier all mostly related for e.g. latitude,longitude,speed,date time,device type,device serial. So what is your idea of normalisation then we will end up with another problem of join? How to weight between to use join or not? –  newbie14 Jul 2 '12 at 14:52
    
@newbie14: In the case of device type and device serial, normalization would pull those two fields plus a new primary key column into a new table, probably called Device or Devices, and then you would put a DeviceId column in the main table. As far as system impact goes (performance or otherwise), it depends on the items I listed in my answer. In this case, though, normalization would make the fact table narrower/smaller, which is almost always a good thing. –  Jon Seigel Jul 2 '12 at 16:15
    
yes we already have a table called device in place which keep the details of the device eg. deviceID, deviceSerialNumber etc. –  newbie14 Jul 4 '12 at 5:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.