I am working on some valuelists and examing some of the crm stuff. I
like the idea of having one SQL table with multiple value lists but I was
wondering if there are any downsides to having different value lists share
one pk? I notice that is how the crm is set up with just a few valuelists
and four or five values. But is there any possible problem if one has
many valuelists (columns) and those different valuelists might all be used
on the same form? Alternatively one could have multiple valuelists in the
same table but each row would only hold one value list. I like the first
way of doing it as it means a neat and tidy table jampacked with
information! But I am just wondering if there could be any downside to
multiple values from different valuelists sharing the same PK and thus I
should limit each row to just having the values for one value list.
Hmm, I don’t see any issue other than sharing the same pk/record means that if you delete/change a record that would count for all valuelists. And that could (but not necessarily would) be unwanted behavior.
I prefer to do all valuelists in seperated tables.
I started too, all my valuelists in the same table, but I came to a point, that it was necessary to delete items. (really delete! not clearing an entry!) So I had to rebuild the whole thing.
Yes, I can see where on certain valuelists I might want to delete a row
thus affecting other valuelists. Some valuelists won’t change other than
being added to (say telephone area codes in the US) but for others that
would not necessarily be true.
What about the idea of keeping all valuelists in the same table but having
each row be uniquely created for a single value list and single value?
This obviously means many null values on every row. But if the
valuelists are relatively short (say no more than a few hundred at most)
then the overall row count would still be quite small. Somehow I just like
the idea of having the valuelists in one table (even though I haven’t done
it like that in the past). But do you think that that would adversely affect
performance having that many null values in the value list table? Any
thoughts appreciated!
Keeping all value lists in one table is what I did in FileMaker to reduce the amount of files. To a certain point this was OK. But everytime you needed this special information for one or another value list, you were either adding fields or tricking around (what we did).
In my opinion the best about SQL databases (and FileMaker 7) is that the amount of tables is not the issue. With Servoy I create tables for value lists even if there is only 3 entries. I think there is absolutely no reason to “spare” tables.
And since you usually want to add constraints to such tables so deleting a value list item is not possible if still used etc. you actually have no other way than using several tables. I think it is not proper relational design to fill information that has nothing to do with the next record in one table.
Thanks for your input. I have always worked with separate value list
tables in our Sybase database but thought I would explore the idea of
storing multiple valuelists in one SQL table. But on thinking about it more
with your feedback I think I will stick with having each valuelist in a
separate table. Thanks!