[TriLUG] OT: Opinion Poll: Database design

[John Franklin]

On Jul 20, 2004, at 9:57 AM, 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.

I agree with Bob.  Go with an OOD approach and things will be much 
easier.  I prefer to give each table an ID field (autoincrement, 
unique, primaryindex) that is called <table>_id and then use <table>_id 
in other tables when I'm referencing it.  Makes understanding the 
tables later on easier and allows for auto-joins, i.e., table_a 
autojoin table_b implies (where table_a.a_id == table_b.a_id).  (The 
syntax here is almost certainly wrong.  It's been near a year since 
I've touched any DB stuff.)

jf
-- 
John Franklin
franklin at elfie.org
ICBM: 38º 56' 32.6"N 77º 24' 47.7"W Z+62m
-------------- next part --------------
A non-text attachment was scrubbed...
Name: PGP.sig
Type: application/pgp-signature
Size: 186 bytes
Desc: This is a digitally signed message part
URL: <http://www.trilug.org/pipermail/trilug/attachments/20040801/1775fc05/attachment.pgp>