DB design Q: Many small tables vs. 1 big table? 3

[VBAjedi]

I'm designing a MySQL dbase (my first) to support the webpage I'm designing (also my first!). Somewhere in this dbase I need to store something like 50 short lists of values (between 5 and 20 values each).

I suspect the relational DB purists would say that I should create 50 seperate tables to store these lists in, structured like:
Index | Value

Would it be more practical to create a single "Values" table structured like:
Index | ValType | Value
and keep all the lists in there?

Any thoughts on the pros/cons of each approach?


VBAjedi

 

[sleipnir214]

I don't think your question can be answered as asked. It's difficult to give design advice without knowing what these values are and what you're going to do with them.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[VBAjedi]

Ok. DB is for website. Several tables hold the bulk of the data, but are 20,000 to 100,000 records long.

I want to populate a bunch of dropdown boxes in a search form with possible unique values (for categories like "Widget types", "Warehouse Locations" "Production Managers", etc.), but don't want to query the primary tables for a unique list each time. The possible values for each field are relatively few, and can only be added/deleted/changed by an admin, so they are relatively stable. So I'm going to store a bunch of value lists somewhere. . .

These same value lists will also be used to populate dropdown boxes to guide users who are adding records to the primary tables (so I limit their input to valid values).

VBAjedi

 

[pwesson]

I think the way you described it in your first posting is fine. No sense having a bunch of tables with only a couple of records in them each...

$sql = "CREATE TABLE LISTS(LIST_NO integer unsigned NOT NULL AUTO_INCREMENT, LIST_TYPE integer unsigned default 0 not null, LIST_VALUE varchar(100), primary key(LIST_NO), index iLIST_TYPE(LIST_TYPE))";

mysql_query($sql);

I just used and integer for the LIST_TYPE but you could make LIST_TYPE a char value or something...

Paul Wesson, Programmer/Analyst

http://www.ipaw.net

 

[VBAjedi]

Ok, as long as I'm not creating potentially huge performance issues. . . I'm surprised nobody has piped up from the other side to explain why that approach is a bad idea.

VBAjedi

 

[sleipnir214]

How do these categories relate to other data in the database?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[VBAjedi]

sleipnir,

Either you missed my second post, or I'm missing what you are asking. . . I thought I was describing the relationship these categories have to the rest of the dbase when I said:

I want to populate a bunch of dropdown boxes in a search form with possible unique values (for categories like "Widget types", "Warehouse Locations" "Production Managers", etc.), but don't want to query the primary tables for a unique list each time. The possible values for each field are relatively few, and can only be added/deleted/changed by an admin, so they are relatively stable. So I'm going to store a bunch of value lists somewhere. . .

These same value lists will also be used to populate dropdown boxes to guide users who are adding records to the primary tables (so I limit their input to valid values).

Does that answer your question? These lists are similar to "SELECT DISTINCT" lists from columns in my primary tables that contain a lot of duplication (except the DB is new so it will take a while for all of the valid values to appear in actual data records)



VBAjedi

 

[sleipnir214]

If your tables contain duplicated information, then your tables are not normalized.

Duplicated data should be stored in its own table and your primary table should have a column which relates each row to a table in the category table.

I guess a better question for me to ask was, "What does your data describe?"

The design of a database must match the objects you are describing. It must also best support the way you will query the data.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[VBAjedi]

Thanks for being persistent. This is a HUGE project to be attempting on my first at-bat with MySQL, and I really don't want to make any glaring fundamental design errors! You have stated some excellent general design principles, but you haven't really given your opinion on my question. So:

I don't have whole ROWS duplicated, just some values in a given column. Say I have a table to track widget sales, and this table contains a "Widget_Type" column. There are 10,000 records/sales in the table, but we only sell 10 types of widgets. If I want a list of the widget types we sell, I don't want to have to SELECT DISTINCT Widget_Type on 10,000 records just to get those 10 types. So I need to store that list somewhere else for easy reference (along with 40 or so similar lists).

My question, as I stated earlier, is:
I suspect the relational DB purists would say that I should create 50 seperate tables to store these lists in, structured like:
Index | Value

Would it be more practical to create a single "Values" table structured like:
Index | ValType | Value
and keep all the lists in there?



VBAjedi

 

[sleipnir214]

If you have values in a column that are duplicated, then those values can be moved to another table. In this case, moving the categories to a separate table and referencing the categories by numerical indeces from your primary table. You don't have to use DISTINCT (it's a good idea to avoid using this operator whenever possible), and you don't have to worry about someone's misspelling "widget" when inserting a record in future. Using distinct with multiple spellings of database key values can cause all kinds of headaches.



But your original question cannot be answered as asked. There is no "right" way to design a database -- merely the best way as appropriate to what you need to store and how you need to access it. Unfortunately, I don't have a picture of what you're describing in your data, or how you'll need to reference those descriptions to be able to give more than general advice.

There are times, for example, when storing redundant information in a database can reduce the complexity of or increase the speed of the queries you need to run. It's less efficient in terms of storage, but more efficient in terms of processor usage.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[r937]

VBAjedi, yes, put all your value lookups into one table

much easier to maintain the app that way

by the way, there's no such thing as a relational DB purist -- they either create real-world physical databases, in which case they can no longer be considered purist, or else they don't, in which case the rest of us can realistically say they don't know what they're talking about

;-)

rudy

http://r937.com/

 

[cdlvj]

For my table data, I have created one table,
TableNo int, Sequence int, TableValue(varchar 256), TableLongValue long, and maybe some others like float/double.

So state codes could be table 1 and values
1,1,Texas,43
1,2,Utah,44

Printers could be table 2 printers
2,1,Home Printer
2,2,Basement Printer
2,3,Kitchen Printer

Views are really nice for this as
Create View as select States from TableMaster where TableNo = 1; (may not be syntacally correct)

Unfortunately mysql does not support this yet.

One drawback is if you had requirements for a State to City relation. With views you could do this join probably.

Hope not to confusing.

 

[cdlvj]

Need to add, on States, the sequence would be the numeric state code. And add a AbbreviatedCode char(2).

1,43,Texas,TX
1,44,Utah,UT
etc.

 

[VBAjedi]

sleipnir, thanks for that info - have a star for sticking around to help me out!

Rudy, have a star for a one-sentence answer to my question (and for that observation about relational DB purists!)

cdlvj, have a star for your clear example and for the thoughts on views (which are coming soon to the MySQL masses, from what I've read).


VBAjedi