When are new children tables necessary?

[gephry]

I have probably been thinking about this too long but I'd like some input. I am never sure of how "deep" to go with children; I don't want my data-separation to become too involved. When is too much too much?

My current situation is thus:

1. I have three tables: CONTACTS, LOCKERS, and LOCKS.

2. A lock is assigned to a locker---one lock per locker.

3. Lockers can be assigned to people.

So the correlation is CONTACTS < LOCKERS < LOCKS.

The thing is there can be multiple keys for a lock (in the instance that more than one person is assigned to a locker and each person has their own key).

In a situation like this, should I create a new table (KEYS) to hold related KEY data for each lock, or just enter the lock multiple times into the LOCK table with different key information?

So the new correlation would be CONTACTS < LOCKERS < LOCKS < KEYS.

Thanks!

[comment]

I think it depends on whether keys are individually identifiable (i.e. numbered).

In any case, I would have an Assignments table, where a Contact is assigned a Locker and a Lock (and perhaps a specific Key). That would enable me to keep history of who had what when.

[gephry]

The keys are in fact individually numbered.

Hm, so yea, an ASSIGNMENTS table isn't a bad idea at all. I don't want to keep a history of locker assignments (since I'm already keeping a rather large HISTORY table of another unmentioned LIBRARY table). But I can have the database delete an ASSIGNMENT record whenever the person returns the locker/key. So that'd work just fine.

Thanks comment!

[Søren Dyhr]

I am never sure of how "deep" to go with children

Not nesserarily needed at all ...here's another way to approach it, where you can change the structure simply by adding yet a criteria to the field I call c_tickle's Case( structure, because the structure as such is endless deep!

I act upon input from this file:

http://www.kevinfrank.com/download/county-highlight-in-portal.zip

--sd

one2mnyrecur.zip

[gephry]

Oo now that is cool. That'd be very useful working in some older versions of Filemaker, like version 6. Granted, there's no variables available before v8, but that could be remedied with some calc fields. Good solution Søren (and Kevin Frank of course)!

I was originally thinking of some type of toggle field within LOCKS so I could keep both keys and locks within that table. With your example, I ended up implementing some parts of your solution (although not as in-depth with people, etc). Thanks!