Click here to Skip to main content
15,886,578 members
Please Sign up or sign in to vote.
3.33/5 (3 votes)
Hi all,
In my winform application i use a table (Table1) with a lot of fields (I mean about 160 fields!), which 17 fields of them have foreign key from another table (Table2),so when i want to see the real value of these 17 fields but not the key of them i should make 17 joins between Table1 and Table2.

On the other hand in my application i have a part that allows user to import an excel file to Table1.Before importing i should make sure that the related value to each of that 17 fields from the excel file is already exists in Table2 or not.Then if it exists get its ID from Table2 and if it is not exists first insert that new value in Table2 and then get its ID.

So now i have confused what is the best way in this case?
Normalize Table1 and use foreign key from Table2,
OR
De-Normalize it to have faster performance?
Posted

1 solution

Ok. This is a not a bad question. Let's start with the normalization part. It is a useful, but theoretical thing. In everyday situations you can consider following or not following it for practical reasons.

I can't see your concrete model, but if only a single value is in the other table (like a category), you can consider keeping both tables (the "category" table should be kept to deliver the set of values), but instead of using key based references, you put the actual value in the referring table - but be sure to be consistent. This is useful if the data in that field is not so large.
Let's take an other example: invoices. Invoices contains buyer address, that can change over time, but an invoice has to be kept unaltered after issuing it. So you have to assure, that the data stored related to the invoice is the same even if partner's actual data changes. But you need a key based reference for other reasons. So you can take a mixed approach: keep all partner data in the external table, keep the foreign key in invoices table - but also copy data that has to be kept intact in the invoice table.
As you can see there are several considerations - but you have to decide.

Let's take a look on those 160 fields. There might be a design flow. It is really rare that you can't decompose such a high number. If they are mostly optional fields (properties) you have chosen to put all possibilities in a single table, you should consider following too (also in combination with the above approach): use XML field and those properties in it; or keep the really unique fields in a table, and externalize the properties with an 1:N relation to a table that holds only name-value pairs in addition to the reference. Let's suppose you want to store the data of a pc. You would have a field for processor, motherboard, memory and so on. Than you could decompose it as follows:
COMPONENT_KINDS(ck_id, component_kind_name)<br />
PC (pc_id,....)<br />
PC_COMPONENTS (pcc_id, pc_id, ck_id, value)

So you could have only as many PC_COMPONENT records for a PC as many you need. It is like rotating a horizontal model to a vertical one.
 
Share this answer
 
Comments
M_Mogharrabi 11-Aug-13 3:01am    
Hi Zoltan Zorgo and thanks for your reply.My situation is not like your invoice example, because the data should change in Table1 after Table2 was changed.
Also i can not use your suggestion about decreasing number of fields,because these fields have just 1:1 relation and thay are all have direct relation to each other.
The problem is that i could not decide between these two ways(Normalizing and De-Normalizing) in this specified situation with considering the advantages and disadvantages of these two ways?
Zoltán Zörgő 11-Aug-13 5:42am    
Your decision should rely on the usage of those data. In case of 1:1 relation, you can't really speak about normalization, since that is meant to reduce/eliminate redundancy. There is nothing alike de-normalization, you either normalize or not, there is no real inverse process.
So if your data is that much related, you should keep it in a single table. Still, you should consider storing individual values instead of so large records - but this is useful if at most half of the cells is filled in average.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900