Hi,
I'm trying to build a simple Club managment tool.
I want my database to be as simple as possible.
One address may have one or more family member
One family member may participate in one or more activite.
Somehow I having trouble when I try to have these three tables on the
same layout.
Anyone has an example of something similar?
Any help appreciated,
JyPe

jype <jype (AT) bigfoot (DOT) com> wrote:

I use one table only in this situation, with an record for the familiy
and separate records for members. A field indicates the familiy number:

SNR Name FamiliyNo
1 Familiy A blank
2 Member of Family A 1

Build a relation from SNR to FamiliyNo.

If you want to send one letter (or whatever) per familiy, search for
records where the field FamiliyNo is blank.

You'll need a table for the activities and a cross table where a record
is the participation of a person in an activity.
--
http://clk.ch

In article <1h1u8w4.3b6xda144l2agN%clk (AT) freesurf (DOT) ch>, clk (AT) freesurf (DOT) ch
says...
Hmmm... your definition of simple escapes me. A name field that doesn't
consistently hold someone's name? Leaving a particular field blank
expresses record "class" semantics?... yikes!!

Why not just create 2 tables? familes, and people.
families = <familiyid, familyname>
people = <peopleid, personname, familyid>

and relate the 2 family ids?

Its 2 tables, but its a heck of a lot simpler. It will be easier to
maintain and extend, and is an accurate and clear translation of the
data you are trying to model.

Also, since he says above that he wants to attach address information to
the "Family" record, my version would simply entail adding a field to
families. Yours... well.. I guess you could add an address field, and
just put a value in it only on the "Family" records.

In solution 2, just look at the records in the families table.

Nah. That generates extra tables, we can avoid that!

Instead simply add an activities field to your original table, and
relate it to SNR.

Then generate some new records:

(SNR,Name,FamilyNumber, Activities)

u, Master Activities, <blank>, <blank>
v, SomeActivity, u, <blank>
w, Another Activity, u, <blank>
x, YetAnother Activity, u, <blank>

(We essentially created an "activity family", with each activity as a
member of that family.)

Next we go through each person, and simply add return separated SNRs for
each activity they belong too.

So if billy bob is in both SomeActivity and Another Activity put a:
v
w
in his activities field. (Naturally you would build a clever layout
interface with drop downlists and such, and build the actual return
separated lists behind the scenes.

To find someone in activity w, simply find w's in the activities field.
Or pull up the activity record in question, and go to related records
via the relationship to activities.

Note that to send a letter to each family you can still find all records
with a blank family number, but you'll want to omit the MasterActivities
record -- just hardwire the SNR for it into a scripted find.

Similiarly if you want to find all the people, just find everyone with
something in the familynumber field, but omit the SNR for the master
activties 'family'. Another simple script will automate that for ya.

EZPZ

I kid, I kid... then again... I've had to do some work on a database
pretty much exactly like that once. So somebody thought it was a clever
idea.

jype <jype (AT) bigfoot (DOT) com> wrote:
I seem to have missed the start of this one (thanks to a hopeless
newsserver) and it really depends on how you want to get the data out
(ie. what reports and on-screen displays), but probably the simplest
solution is to have just two tables:

Families
- Address
- Phone
- Email
- Family ID {auto-enter serial number}
- Name 1
- Name 1 ID {auto-enter calculation: Family ID & "-1"}
- Name 2
- Name 2 ID {auto-enter calculation: Family ID & "-2"}
- Name 3
- Name 3 ID {auto-enter calculation: Family ID & "-3"}
- etc.

Activities
- Name ID
- Activity
- etc.

Then you can create a separate relationship for each person as:

Families::Name 1 ID <-> Activities::Name ID
Families::Name 2 ID <-> Activities::Name ID
Families::Name 3 ID <-> Activities::Name ID
etc.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

42 <nospam (AT) nospam (DOT) com> wrote:

[Flat address file with families and family member using auto-relation]
I learnt that in this group and found it useful.

I see the table as an address file. The Name field will hold the name if
it's a person, the name of the company if it's a company and some
familiy name if it's a familiy - since we want to use families to group
the records.

Because I wouldn't want to have two tables in this case.
--
http://clk.ch

In article <1h1uqy2.k7ufv0u9xtu4N%clk (AT) freesurf (DOT) ch>, clk (AT) freesurf (DOT) ch
says...
If we have the address in the address record *and* in the family member
record(s) then were redundantly copying data. If we don't use the field
when its a family member then we're making the logic of actually using
the table confusing.

Exactly why would you value a single table over a clean design without
an overriding external consideration?

For example some FM6 internet hosts charged by the table, so it was
often cost effective to spend extra work on design and maintenance of
inelegant systems to save money on the hosting. Or you could run into
the open file limit, and then you HAD to start condensing. But failing
that it makes no sense.

Its like keeping your documents, your programs, and your operating
system all in one folder. It doesn't make things easier. Its just a
mess.

42 <nospam (AT) nospam (DOT) com> wrote:

[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....rowse_frm/thre
ad/2f2464128581dbd4/104f47ed6f70b16a?lnk=st&q=group:comp.databases.fil em
aker+insubject:flat&rnum=1&hl=de#104f47ed6f70b16a>

--
http://clk.ch

In article <1h1vajm.w2pw68rdiw1wN%clk (AT) freesurf (DOT) ch>, clk (AT) freesurf (DOT) 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 documenting' .. 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