If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Tim,
I am sorry I have been unclear. Here are the main entities. Family (Case File) Family Members (Clients) Family Contacts (Non-Clients) Addresses Phones ... there should be one person attached to a file with primacy, and that there should be one address linked to that person with primary, then we are back on track. Yes this is what I mean. I should have clarified better from the begnning. The main purpose of the db is to track Families (Case File). Within each family they want to track Family Members (Clients-which they are calling Personal Journals will leave that out for now). Basically they (the people I am building the db for) want the Family as the first enity, and then within the Family the want to track the Family Member (Client) as a unique enity (A file within a file). The contacts are strictly contected to the family entity. I hope this helps and does not muddle it more. -TFTH Bryan |
#12
|
|||
|
|||
Tim,
This is what I have now Family MFIDS PK (Auto Number) FID Long FJID TEXT (MAIN ID for table) People IDS PK (Auto Number) PJID PK FN LN MI Type (family member, or type of non-family member) Primary (Use as primary contact) Main (Use as main family name) Personal (Client) IDS PK (Auto Number) PJID PK DOB etc., Addresess IDS PK (Auto Number) FJADID PK FJID FK Address1 Address2 City etc., Resides IDS PK (AutoNumber) PJID FK FJADID FK Phone IDS PK (Auto Number) FJPNID PK FJID FK Phone Type Primary Call IDS PK (Auto Number) PJID FK PJPNID FK Primary Does this look right? That way there can be one address for many people. I can check for a address for a Person (Client) and if there is non found then a query can find the primary address for the Family File. -TFTH Bryan |
#13
|
|||
|
|||
"Bryan Hughes" wrote in
: The main purpose of the db is to track Families (Case File). Within each family they want to track Family Members (Clients-which they are calling Personal Journals will leave that out for now). Can I make a plea -- that you think really hard about your naming strategies? In six months' time, you (or, even worse, somebody else) will be staring at acronyms like PJID PK and wondering what on earth it means. Typing a little bit more now will save a huge amount of effort later. If that means PersonalJournalID then call it that. And don't call it Families sometimes and Files another time. In my opinion, it's better to stick to simple words like People and Addresses and LivesAt for the same reason, and because also they are free from implications about functionality. I agree that that is a matter of personal style and taste, though. DB design is all about semantics: the best solution often comes down to questions like "What exactly is an address (for mailing? visiting? identifing? liability for services?)" Don't worry about going back to your people and asking picky questions like "what _exactly_ are you going to use this DateOfBirth for?" It makes a difference. This is what I have now Family MFIDS PK (Auto Number) FID Long FJID TEXT (MAIN ID for table) Phrases like "Main ID for table" don't help. If it's a unique and stable identifier, then it's the primary key; if it's not then it's just a field like all the other ones. You don't give any clue about the use or meaning of MFIDS or FID so I can't comment. People IDS PK (Auto Number) PJID PK FN LN MI Type (family member, or type of non-family member) Primary (Use as primary contact) Main (Use as main family name) If only clients can be Primary Contacts, then the Primary field belongs in the Clients table. I don't see any foreign key pointing at the Files (sorry, Family) table: how do you know which file this Person belongs to? You have two fields labelled PK -- you can't have two PKs in a table, and there is no point in using an autonumber as part of a compound PK, because an autonumber is already stable and unique. Is a Main Family Name an attribute of the person, or of the Family (and thus of all the People in that Family)? Personal (Client) IDS PK (Auto Number) PJID PK DOB etc., Hmmm: my original suggestion made this a subtype of the People table, so the PK would be a Long Integer that matches the autonumber PK of the People table. You can't point an autonumber at another autonumber because you can't choose what to put in it. Addresess IDS PK (Auto Number) FJADID PK FJID FK Address1 Address2 City etc., I still don't understand what all the fields labelled PK are for. If this is a straightforward Addresses table, then you just need an autonumber PK and the text fields. Phone IDS PK (Auto Number) FJPNID PK FJID FK Phone Type Primary Ditto Resides IDS PK (AutoNumber) PJID FK FJADID FK All you really need in this table is two fields: a Long Integer pointing at the People table and a Long Integer pointing at the Addresses table. You need some kind of AddressType indication somewhere. If a particular address is always (R)esidential or (W)ork etc, then you can keep it in the Addresses table. On the other hand, if Jones resides at Tolpuddle Flats, while Smith works there, then it's an attribute of the Resides table. If you see what I mean. Call IDS PK (Auto Number) PJID FK PJPNID FK Primary Ditto: I assume this is the table that tells you which PhoneNumber belongs to which Person. There is quite a lot there. I get the impression that you need to understand more about keys and what Primary Keys do. It's not just a question of stuffing an autonumber field in all the tables: you need to have some conception of what you need to achieve. All the best Tim F |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Design View problem Access 2003 | Alex | Using Forms | 1 | June 22nd, 2005 09:55 PM |
Table Design Problem? | B Karthick | General Discussion | 1 | April 16th, 2005 09:36 AM |
Problem found when exporting e-mails to OL2003 | Sheldon Tam | General Discussion | 0 | April 5th, 2005 11:41 PM |
Problem Bullet Point - Layout or Design? | John | Powerpoint | 1 | June 29th, 2004 04:30 AM |
Productkey problem when installing office 2003 on network | Stefan Schreurs | Setup, Installing & Configuration | 1 | June 1st, 2004 11:16 PM |