[TriLUG] OT: Opinion Poll: Database design
nc37 at nc.rr.com
Tue Jul 20 10:33:31 EDT 2004
The right way to go about is (b) - you want to preserve referential
integrity of your data and guarantee no duplicates. One id/key for each
piece of unique info, only one copy of each piece of info. Even if it
seems like an overkill to start with, makes everything maintenable. It
also helps to keep the db consistent even if the app misbehaves.
Brian Henning wrote:
> Just wondering what people's opinions are on the following topic:
>a) One table with copious fields
>b) Multiple tables with fewer fields each, linked somehow
>a) One table with 30 fields containing all possible customer data (name, 2
>addresses, 4 phone numbers, e-mail, payment method, etc)
>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.
>Brian A. Henning
More information about the TriLUG