Hi,I have a question on the best way to lay out a table structure. Although I like structure 1 from a relational point of view, from a shear performance point of view structure 2 might be better. I am very comfortable working with 5-10 million record tables but with option 1 the tables could be easily 50 million, so I wanted to get another opinion.This is releated to insurance rating and vehicle VINs. Each year/make/model has a unique VIN. Each insurance company may assign special "info" about that vehicle. There could be 1-6 pieces of special information per VIN (this is based per insurance company)Structure 1Table 1companyID (int - compound key)VIN (varchar - compound key)infoType (varchar - compound key)infoValue (varchar)sample recordsCompany VIN InfoType InfoValue 1 1212121 BI 011 1212121 PD A71 1212121 COMP 072 1212121 BI 043 1212121 COMP 323 1212121 COLL 3U
Since we are using name/value pairs we have complete flexibility with InfoTypes and don't have null recordsDownside, due to the shear number of record/combinations this table could get to be 50 million+ recordsStructure 2Table 2companyID (int - compound key)VIN (varchar - compound key)BI (varchar)PD (varchar)COMP (varchar)COLL (varchar)sample recordsCompany VIN BI PD COMP COLL1 1212121 01 A7 07 null2 1212121 04 null null null 3 1212121 null null 32 3U
Since we have specific columns for each type of info, the table strucure is more rigid but will be much smaller (2 million records). The problem is we now have multiple columns with lots of nulls (indicating poor design). Another problem is some companies have realy crazy info types. Instead of adding more columns we could have 1-2 additional columns which would store this random info on a per company basis and this column becomes more of a catchholder which is necessary since the InfoTypes are predefined.In the end, I like the flexibility of structure 1, but due to the size, structure 2 might be better. The queries to this table need to be very quick. From a speed/size point of view (with millions of records), what is better?What are people's thoughts?Note: we will need to have joins to this table etc so size could be more of a problem.Nic
Data Structure question
Posted - 2007-10-04 : 15:28:58
