Subject Re: [ib-support] Design question
Author Helen Borrie
At 07:55 AM 03-04-02 +0200, you wrote:
>Hi all,
> I'm wondering already long time what is the best aproach. Most
> of the
>time a table starts with a limited number of fields. Then user A wants to
>have additional info, then user B pops up with some other fields, and if you
>always add those fields in the same table , after a few months it looks like
>a mess. Sometimes you start with 20 fields and before you know, you have a
>table with 60 fields. I don't feel so good about this. For example I
>have a customer table, with already 50 fields. Everything is in one table.
>Now the question is, should I split up fields in seperated tables ? For
>example, in my CUST table, there are 8 fields to hold delivery information,
>should I split up that information in a CUST_DELIV table ? I just doubt
>about those things, because I like "clean" work.
>Any good :-)))) ideas are welcome,


The thing about a relational database is that you have the flexibility to
add more *relations* (tables) without impacting anything else (existing
applications, dependencies, etc.) If one has a primary table like Customer
which grows from 20 to 60 columns, then that indicates deficiencies in the
analysis and design phase. Sometimes these are inevitable (you took over a
system designed by a spreadsheet-jockey with a shop-bought MBA and no
ability to think abstractly [<g>]...)

Your example, Customer delivery addresses, is one I would design right out
of the primary table from the start. Many customers have multiple delivery
addresses so you *need* a 1:Many relationship - otherwise, you are stuck
with kludges like having to create a separate customer entity for each
delivery address... likewise things like contact names, phone numbers,
discounts, etc. - nearly always ending up needing 1:Many.

If you are smart about primary keys, e.g. ALWAYS surrogating them to avoid
stuff-ups when non-atomic keys get changed by external forces (a company
changes its name, an employee gets married) you can plan for future changes
in requirements by allowing for possibilities and probabilities. Keep
primary keys atomic and small and you will be able to FK 1:1 and 1:Many
relations as much as future needs require.

Always keep in your mind that, when users want a new column, they want this
new column in OUTPUT. As a database designer, you don't base your table
structures on what the user wants to see (reports and screens) but on what
will store and retrieve safely and efficiently in your database...


All for Open and Open for All
Firebird Open SQL Database · ·