April 22, 2009 at 8:19 am
Hi guys and gals,
Sorry for the elementary question here, however, I have my brain so wrapped up on this subject I can’t push myself to get pass it.
Question: What/Where is the tipping point to create another table as opposed to having a table with many empty fields. For example: I have a table that will hold hardware and software. The table has a number of fields that would pertain to both items. At what point do I say ‘Hey, this table is going to have hundreds of empty fields…I should separate both items… each into its own table.”
So although both items have attributes that they share there are also many fields that would remain empty because they would not pertain to the item being entered. Where is the balance?
Am I making sense?
Thanks in advance!
April 22, 2009 at 8:28 am
so you have 'hardware' data and 'software' data?
lets say that they have 5 rows that can apply to both hardware and data.
hardware has another 5 rows that software doesn't
and software has another 5 rows that hardware doesn't.
is this the sort of scenario you have?
i'd have two seperate tables in this case
it would probably make it easier to read and query the tables as well if they were seperated.
April 22, 2009 at 8:37 am
that is exactly what I am talking about. How do you manage that or is it a matter of preference? At what point is it ok to have 'some' empty fields and at what point is it not ok to have that many empty fields?
April 22, 2009 at 9:37 am
i suppose its up to your discretion.
personally, if there are columns that aren't being used by hardware but are by software and vice versa then i'd put them in different tables.
take this as an example:
you have a column (col1) that gets filled by hardware data. this column cannot be null
you also have software data within the same table. the software data does not need to fill col1. you could potentially have a problem.
i just think it would be easier to read and support if you split them into two tables.
maybe someone else has a different idea though.......
April 22, 2009 at 11:30 am
Also think about the understandability and usability of the table. Theoretically, you could put all data in a single table, but it would be real difficult to understand and use.
Since hardware and software are quite different objects, it would make sense to split these into 2 tables, unless you are more interested in attributes which could apply to both such as who owns it, where it is located, how much it cost, etc (attributes which could apply to anything).
If it was easy, everybody would be doing it!;)
April 22, 2009 at 12:30 pm
so if we are talking about servers and software attributes would it be a good idea to have a 3rd table that has all the attributes that are common and then the 2 seperate tables for all the other attributes that are not common?
April 22, 2009 at 12:39 pm
Why do you think you need to put common attributes in one table? If I had 2 different entities Person and Building and both entities had an address associated with them, would I put both entities in the same table? No, I would model each entity in it's own table. In this example, I would have a third table called Addresses that both the Person and Building entity would reference. This is called normalization. Will this work for your example? We can't tell you because we don't know enough about your Software and Hardware entities to tell you.
Why don't you post the table DDL and explain which columns are commong and which ones uniquely identify your hardware/software entities?
April 22, 2009 at 1:37 pm
Otoniel Cantu (4/22/2009)
so if we are talking about servers and software attributes would it be a good idea to have a 3rd table that has all the attributes that are common and then the 2 seperate tables for all the other attributes that are not common?
whn you think about having a 3rd 'central' table you should consider normalization. if a 3rd central table would remove the need to have a lot of duplicated information in the other two tables then go for it. if not then it probably isn't necessary
April 27, 2009 at 5:34 am
Why do you think you need to put common attributes in one table? If I had 2 different entities Person and Building and both entities had an address associated with them, would I put both entities in the same table? No, I would model each entity in it's own table. In this example, I would have a third table called Addresses that both the Person and Building entity would reference. This is called normalization. Will this work for your example? We can't tell you because we don't know enough about your Software and Hardware entities to tell you.
Thats what I use to do in my designs. The concept may be termed as IS-A Relationship. The common attributes belong to the Master/Main Entity and according to the rest of the attributes it is decided wheather it belongs to Software or Hardware. Also (to keep things simple) a bit can be set in Main entity to mark it as Hardware / Software.
April 27, 2009 at 2:58 pm
I understand the super/sub type modeling technique and I know that it is a valid method for modeling data and that it is used all over the place. I do think, however, that it is overused. I often hear how people want to genericize their data and they use this modeling technique as a way to do that, but it is easy to get into a scenario where you are no longer modeling an entity and your tables begin to look more like spreadsheets.
One of the questions that you should ask yourself is why you would want to do this? Do you think you are saving time or space? Do you believe that it makes querying the data easier? I would disagree to both. I think it adds complexity and reduces performance.
Once again, we don't know enough about the problem to offer a complete solution. The OP needs to post some sample data and an example of what is happening to give solid advice. Generally speaking, model the entities in their own tables.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply