Andy, this is very similar to what I do:
Structure for table: C:\HIFISNET\DATA\CLIENT.DBF
Field Field Name Type Width Dec Index Collate Nulls
Next Step
1 PK_CLIENT Integer (AutoInc) 4 Asc Machine No
119 1
2 FK_CLIENTF Integer 4 Asc Machine No
4 FK_LKGENDR Integer 4 Asc Machine No
5 FK_LKFAMRO Integer 4 Asc Machine No
6 FK_LKRELAT Integer 4 Asc Machine No
7 a whole bunch of other fields
FK_ClientF is the PK of the family head. If you're single, it is equal to your
PK_CLIENT
FK_LKFAMRO = 0 for Single, 1 for FH, 2 for Partner and 3 for Dependent
FK_LKRELAT is the relationship to the FH (son, spouse, etc...)
The result is all data in the same table and the FH is someone you 'hook onto'.
Of course, FH is the family 'leader'. Can be anything... Family cannot exist
without head.
Would something like this work for you?
Henry
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andy Davies
Sent: 2006-09-18 11:16 AM
To: [EMAIL PROTECTED]
Subject: data[base] design
Hi,
I wonder how people would handle this design:
family >--- property/ address [1/many to 1]
family ---- head of family [1 to 1]
family ---- 2nd contact [1 to 0/1]
family ---<other members [1 to 0/many]
family ---<other stuff [1 to 0/many]
Note: property ---< family [1 to 0/many] i.e there is already a
property table, not all properties will have a family record (most won't)
head of family/ 2nd contact/ other members all share similar attributes
(dob, gender, school, employer, ...)
I have considered having one 'individuals' table which includes flags for
'is head of family', 'is 2nd contact' etc.
which has its attractions but would seem to break normalisation rules (a
fixed 1to 1 relation split across two tables)
and might be difficult for referential integrity (a family must have a
head)
The alternative of having 'head of family' info in the family table and 2nd
contact/ other member info in the 'individuals' table seems messy.
Comments please.
Andrew Davies MBCS CITP
- AndyD 8-)#
PC Development Officer
Housing IT
Housing Services
Manchester City Council
@Hawthorn Cottage
( Office +44 161 234 5330
( Internal [814] 5330
( Mobile 0775 938 5155 (when all else fails)
* [EMAIL PROTECTED]
* home [EMAIL PROTECTED]
**********************************************************************
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed. If
you have received this email in error please notify the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
Please contact [EMAIL PROTECTED] with any queries.
**********************************************************************
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
