On Wed, Jul 16, 2008 at 8:40 AM, Haim Ashkenazi
<haim.ashkenazi@gmail.com> 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.