clk@freesurfch christoph

[Flat address file with families and family member using auto-relation]

First of all, I'm not a professional database designer, so there's no
need to take anything I say or do seriously. I only posted the proposal
because no one else had replied at the time. I'm sorry for the confusion
I caused.

As for this solution, I adapted an idea posted by Matthew Smith on Jan
26, 2003. The thread is archived here:
<http://makeashorterlink.com/?A2CD51DAB>

The full URL is: <http://groups.google.com/group/comp.databases.filemaker/browse_frm/thre ad/2f2464128581dbd4/104f47ed6f70b16a?lnk=st&q=group:comp.databases.fil em
aker+insubject:flat&rnum=1&hl=de#104f47ed6f70b16a>

--
http://clk.ch

42

In article <1h1vajm.w2pw68rdiw1wN%clk@freesurf.ch>, clk@freesurf.ch
says...

No need to apologize. I regret it if I came on too strong. I didn't mean
to offend.

However, speaking from a 'relational data modelling' perspective its a
terrible design. Good databases, like any software engineering are
ideally 'self do***enting' .. that is... ideally you should be able to
look at the schema (the tables, the fields, the relationships) and
immediately understand the various entities and relationships that are
modelled.

As soon as you start doubling up different multiple entities in a single
table you invariably end up re-using fields for different purposes
(different semantics), and you often have extra fields or criteria to
idenity what the entity actually is, and which semantics to apply to the
various fields. All this extra stuff violates all kinds of data
modelling "rules", prevents the database from ever being normalized, and
so forth.

All databases must make concessions to perfect form, in order to satisfy
objectives like competing performance, hosting price (in the case of FM6
per table pricing models), or limitations of the database engine itself
(maximum number of tables, open files, indices, maximum number of tables
that can be included in a join, etc...)... that's just part of life.

But you should never *start* with a compromised design. And under
filemaker 7, the number of times I've had to make concessions has
dropped drastically.

At any rate. The simplest database designs are not those that use the
fewest tables and fields; they are the ones that reflect the data model most accurately.

Its a common enough technique. And under earlier versions of FM6 where
the engine limitations were much greater it made sense if you just
wanted a quick and dirty solution to tack onto a much larger system,
where you couldn't afford generating any extra files. With FM7s multi-
table-per-file support this is usually (always?) no longer a
consideration.

I recommend you look into data modelling; even just getting a handle on
the basics of 3rd and 4th normal form, and E-R (entity-relationship)
diagramming will likely serve you well.

Like all software development the most important decisions are made long
before you create your data tables, during the specification and design
stages.

best regards,
dave