On Wed, Jul 16, 2008 at 8:40 AM, Haim Ashkenazi <haim.ashkenazi@xxxxxxxxx> wrote: > Hi > > I'm creating an application that have an fixed length alphanumeric key > and each key has (currently) up to 5 fixed length alphanumeric > variables. I was wondering which of the designs would be faster > considering that I could have about million keys... > > The first design is the obvious one. create one table with ID and > "KEYNAME", and a second table which uses the ID of the first table as > foreign key to list values. > > The second design is to use one table with the columns: "key", > "value1", "value2", etc... > Since it's highly unlikely that we'll ever change the number of > values, I'm ready to consider this option if it proves to be much > faster then the first design. (although I do prefer the first design > if the speed improvements is minor). > > Any ideas? Several. It depends on how you'll be using the data in the future. Are you likely to access the key by itself? Like just to see if a range of keys exists or something? Or something like that? Then having a table with just the keys in it, that points to the values table might be useful because you won't have to retrieve a bunch of values you don't need. Is it more likely that you'll always access key and all 5 vars at the same time? Then put them all in one table. If it's likely you'll need to add new value fields, then use three tables, one with key and another with values and a table in between to join them up based on key to value(s) for the values you need. More versatile, probably the slowest of all. And finally you have star schema. That's where you make one table with keys, and a table for each value type, and you join to the value tables as needed. This is similar to the previous method, except that you now create a new table for each value. The advantage is that you only have to join to the tables for values you need, and it's usually pretty fast. So the answer is that it depends on how you'll be accessing your data and updating it. Each method has advantages over the others for certain use cases. I'd suggest picking up a book on database design and getting familiar with it. A little time spent now will save you a lot of headache later on.
