[TriLUG] OT: Opinion Poll: Database design

[Scott Chilcote]

Brian Henning wrote:

> Howdy Folks,
>   Just wondering what people's opinions are on the following topic:
> a) One table with copious fields
> -vs-
> b) Multiple tables with fewer fields each, linked somehow
> ex:
> a) One table with 30 fields containing all possible customer data (name, 2
> addresses, 4 phone numbers, e-mail, payment method, etc)
> -vs-
> b) One table with names and IDs (2 or 3 fields), one table with addresses
> (11 fields), one table with contact info (6 fields), one table with payment
> data (4 or 5 fields).
> 
> Note that this is intentionally an example where there is no need for
> many-to-one mapping, with the exception of two addresses (billing and
> shipping) to one customer, so the many-to-one ease facilitated by option (b)
> isn't much of an incentive in this case.
> 
> This is entirely academic, as I've already made my decision for the database
> in question..  I'm just curious what others think, especially if any
> opinions happen to address performance vs. storage space -type issues.
> 
> Cheers,
> ~Brian

Hello Brian,

My experience has been that the logical organization of the tabular data 
usually falls out of the specification and design process.

A primary consideration is how the data will be accessed, not only for 
generating reports but for updating (if updates are done frequently). 
Will specific views on the data be required frequently?  Will a 
particular key composed of one or more columns be used very often for 
searching and/or sorting?  Will a set of columns be used very rarely?

If a single column of the table will be used as the search field, e.g. 
customer number, a single table is not such a bad idea.  The column can 
be used as the index, and access will remain fairly efficient.

If two or more columns will be used this way, it's a better idea to look 
for a logical way to divide the table so that the two tables can be 
indexed individually if possible.

Another major concern is how large the table(s) are likely to become. 
Allow generous room for growth.  A wide table with multiple indexes 
isn't a major problem if the number of records will remain under a few 
thousand.

Keep in mind that it is not terribly expensive to restructure your 
database if it becomes inefficient.  If it's being accessed around the 
clock it will take careful planning, but I've seen it done successfully 
several times.  It's very likely that the data will need to be used 
differently over time, and that some columns will need to be dropped and 
new ones added.

Having said all this, there are likely to be online sources for database 
design theory that tackle this subject at far greater depth.  Stay 
tuned...  :)

Good luck with your project.
___

Scott C.