 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / August 2005Design Problem
Hello, I have a Case File management db. There can be multiple clients in each case file. Each case file has a primary contact. Each client also can have separate addresses and address history. It is possible that the Primary Contact is not a client. How should set this up that if the Primary Contact is a client if the address is changed in the client area the primary contact is updated, or if the address is changed in the primary contact then the address is changed in the client area. -TFTH Bryan I need to clarify. What I meant to ask is how should I do this so I am not storing reduntant information. > Hello, > [quoted text clipped - 13 lines] > -TFTH > Bryan > What I meant to ask is how should I do this so I am not storing > reduntant information. <rant class="screeching"> EEeeeek: the point of R design is not to prevent redundancy -- in fact, in any non-trivial database there are huge amounts of repeated and replicated stuff. This is a point that has been entirely lost by acres of crappy web pages put up by people who just don't know what they are talking about. The point of R design is about integrity -- the ability to _prove_ that information contained is complete and consistent. It's wasteful, slow, and resource-hungry, but it's *correct*. </rant> Anyway, to answer your question, you start by identifying the entities you want to model; then ascertaining their attributes; then defining how they relate to each other. From your original post, I would be guessing a minimum of something like: Cases(*CaseID, EnglishDescription, DateStarted, EstCost, etc) Contacts(*ContactNumber, FName, LName, CreditLimit, etc) Addresses(*AddressID, FirstLine, SecondLine, PostCode, Phone) LiveAt(ContactNumber+, AddressID+, IsPrimary) Clients(*CaseID, *ContactNumber, IsPrime) but I would guess the whole thing gets a lot more sophisticated after that. Sorry for the rant. Hope it helps Tim F Thank you, That helps immensely. How do I deal with there is only be one Primary Contact, but multiple entries for client contact information. tblCase_File(*CaseID, FN, LN, etc.) tblPrimary_Contact(*PContactID, CaseID+, etc) tblClient(*ClientID, CaseID+, FN, LN, etc) tblClient_Address(*AddressID, ClientID+, etc.) tblClient_Phone(*PhoneID, ClientID+, etc.) Should I make a table that holds address and table that holds phone and connect them with ID information? Please help slow on the uptake. >> What I meant to ask is how should I do this so I am not storing >> reduntant information. [quoted text clipped - 31 lines] > > Tim F > tblCase_File(*CaseID, FN, LN, etc.) If FN and LN are names, then surely names belong to people, not to files? Should not these data belong to the Clients table? You need (a) a ClientID FK here if each file belongs to one client, or (b) a CaseID fk in the Clients table if each client has exactly one file, or (c) a new table if clients can have many files and files can have many clients. > tblPrimary_Contact(*PContactID, CaseID+, etc) Don't understand this: it means that each Case_File can have zero or more Primary_Contacts, which I guess is not what you want. > tblClient(*ClientID, CaseID+, FN, LN, etc) See above for comments on the CaseID field... > tblClient_Address(*AddressID, ClientID+, etc.) > tblClient_Phone(*PhoneID, ClientID+, etc.) This works if your clients never share addresses or phones; but with families, corporate addresses etc you may want to rethink this bit. > How do I deal with there is only be one Primary Contact, but multiple > entries for client contact information. There is not a good pure-R model for this. Ideally, you would have an column in the IsAContactFor table called IsPrimary -- the trick is to make sure that each Case_File has at most one record with this set to true. In SQL Server you can do this with a trigger, using Jet and ADO you can set a CHECK constraint, but with Jet/DAO you can only use code in the form/ module. Hope this helps Tim F Tim, Thanks >If FN and LN are names, then surely names belong to people, not to > files? Should not these data belong to the Clients table? You need (a) a > ClientID FK here if each file belongs to one client, or (b) a CaseID fk > in the Clients table if each client has exactly one file, or (c) a new > table if clients can have many files and files can have many clients The tbCase_File has a FN and LN field for the case file name. It is possible that this name could change (client gets married, etc.). In the tblClient there is a FK for the CaseFileID. There is one case file with many clients in it. > Don't understand this: it means that each Case_File can have zero or > more Primary_Contacts, which I guess is not what you want. Each case file has one Primary Contact, but it is not always a client (Non-Family Member). The tblPrimay_Contact has the CaseFileID as a FK. It also has FN, LN and ClientID field if the primary contact is a client. > This works if your clients never share addresses or phones; but with > families, corporate addresses etc you may want to rethink this bit. What I have done is create a tblAddress and tblPhone, each entry has a CaseFileID to contact it to the right case file and each entry has a unique id. I am trying to use this for a single table for my Primary Contact address, Client address, Emergency Contact etc. Should I create another table that connects this to the correct AddressID to the correct ID(client, primary, emergency, etc.)? I know I can link a single address with the case file ID but how can I make the connection to a particular client, or other type of contact? -TFTH Bryan >> tblCase_File(*CaseID, FN, LN, etc.) > [quoted text clipped - 32 lines] > > Tim F > The tbCase_File has a FN and LN field for the case file name. It is > possible that this name could change (client gets married, etc.). ... so surely the file name has to update to reflect that change? In other words, the names belong to the client rather than the file. Please don't tell me that you identify the file by name alone... "Hello, I am Mrs Lizzy Reardon, but my file is called Elizabeth Smith -- no, not that Elizabeth Smith, the uptown one..." I see you have a CaseID and presumably this matches the _actual_ file identifier: stick to the Real World wherever possible. > Each case file has one Primary Contact, but it is not always a client > (Non-Family Member). Okay -- we have a new entity type now, the ContactWhoIsNotAClient. This sounds like a candidate for sub-classing (more later). What are the differences between a Client and a NonClient? > What I have done is create a tblAddress and tblPhone, each entry has a > CaseFileID to contact it to the right case file and each entry has a > unique id. As said above, this is theoretically fine as long as contacts never share phone numbers and addresses. You are leaving yourself open to errors like Mr Long getting to live at 12 Railway Cuttings whilst Mrs Long is still at Flat 13b Nelson Mandela Court. If exactly one address belongs to exactly one person, why not just put the address data in the Contacts table anyway? If you have lots of addresses for a particular contact, how will you know which one to use in any particular context? > Should I create another table that connects this to the correct > AddressID to the correct ID(client, primary, emergency, etc.)? It depends -- what real-world entity is this new table modelling? ---- Stepping back a little, it seems to me that you are designing by function, rather than by entity. I suggest you need to sit back with a blank piece of paper and write down all the entities that you have to model. For starters, what you have mentioned already include:- Files People Clients NonClients Addresses PhoneNumbers Hope it helps Tim F Tim, This is what I have came up with so far. tblFamily_Journal PK MFIDS AutoNumber FID Long Increment of 1 FJID Text (Unique ID) FN LN Open_Date tblFamily_Journal_Contact PK IDS AutoNumber FK FJID FJCTID Text (Unique ID) FN LN Type (Type of Contact, Primary, Emargency, Additional) Relationship tblPersonal_Journal PK IDS AutoNumber FK FJID CNID Interger (Used to create the PJID) PJID Text (Uniquie ID) FN LN Primary Boolean etc. tblFamily_Journal_Addresses PK IDS AutoNumber FK FJID FJADID Text (Unique ID) Address1 Address2 City State ZipCode Type Date_Add Current Boolean tblFamily_Journal_Phones PK IDS AutoNumber FK FJID FJPNID Text (Unique ID) Phone Type Date_Add Current Boolean >Please don't tell me that you identify the file by name alone... The Family ID (FJID) is the main file identifier. There is also a aka table that tracks name change history for clients >Okay -- we have a new entity type now, the ContactWhoIsNotAClient. This >sounds like a candidate for sub-classing (more later). What are the >differences between a Client and a NonClient? A Client is a family member receiving of benefiting form services porvided to the family (demographic and other information is collected for clients). A Non-Client is a external contact for a family but is not a family member (only name and contact information is collected). >As said above, this is theoretically fine as long as contacts never share >phone numbers and addresses. You are leaving yourself open to errors like [quoted text clipped - 3 lines] >table anyway? If you have lots of addresses for a particular contact, how >will you know which one to use in any particular context? I have a addrerss table and a phone table that collects address information for the entire family. One Family Member (client) in the family can have multiple addresses, another can have no addresses entered so they would default to the primary contact address for that family file. How can I connect the addresses to the Family Members (clients) or Family Contacts (Non-Clients)? The main entities for this db are Familiy (Main) Family Members (Clients) Family Contacts (Non-Clients) Address PhoneNumbers -TFTH Bryan I have reordered some parts of the post in order to try to impose some logical progression. Unfortunately, I am not clear exactly how your message maps onto itself: you might be causing some confusion by using different language for the same things. > The main entities for this db are > Familiy (Main) > Family Members (Clients) > Family Contacts (Non-Clients) > Address > PhoneNumbers ... > This is what I have came up with so far. > [quoted text clipped - 4 lines] > tblFamily_Journal_Phones > tblFamily_Journal_Phones and what happened to the files? >> What are >> the differences between a Client and a NonClient? [quoted text clipped - 3 lines] > for clients). A Non-Client is a external contact for a family but is > not a family member (only name and contact information is collected The best way of mapping this is probably to have a single entity for Contacts (I would call it people, but as long as we are both describing the same thing it doesn't matter much). This would contain "name and contact information": ContactID PRIMARY KEY FirstName LastName You would have a second table called Clients linked in this way: ContactID PRIMARY KEY FOREIGN KEY REFERENCES Contacts BirthDate IncomeInDollars ProvisionLevel etc Note that the linkage means that you can have a Contact with or without a Client record, but you can't have a Client who isn't a Contact. >> If you have lots of addresses for a >> particular contact, how will you know which one to use in any >> particular context? You never answered this. > tblFamily_Journal_Addresses > PK IDS AutoNumber [quoted text clipped - 8 lines] > Date_Add > Current Boolean I don't know what a FJADID is, but I am usually highly suspicious of non- primary unique indexes. Yes there are reasons for them, but it's about as rare as 1:1 relationships. I assume that FJID references the Family_Journal table but you said that addresses belonged to people not to files -- it should point to (my) Contacts table so that clients and nonclients can have somewhere to live. Addresses ( AddressID PRIMARY KEY BelongsTo FOREIGN KEY REFERENCES Contacts IsPrimary IsCurrent StreetName CityName etc ) although you can join Addresses to Clients when you need to write reports, queries, mailmerges etc. Make the same arrangement for the phone numbers. > One Family Member (client) in the family can have multiple addresses, > another can have no addresses entered so they would default to the > primary contact address for that family file. "... Address for that family file..." This is the first time you have mentioned addresses belonging to a file rather than to a person. If you mean that 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. Otherwise, we have a different scenario involving one supertype called "ThingsThatCanHaveAddresses" and subtypes called "Files", "Contacts", "Clients" and so on. Files ---< Contacts ---- Clients | | | +----< Addresses +-------< Phones This sums up where we are at the moment, and I think it addresses what I understand of your needs. Some of the queries will not be pretty, but you only have to write them once! I am still worried about this defaulting address stuff: I'll think about that over the weekend and post back if it becomes any clearer. B wishes Tim F 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 > 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) [quoted text clipped - 5 lines] > 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) [quoted text clipped - 4 lines] > 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) [quoted text clipped - 3 lines] > 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 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 Tim, This is what I have came up with sor far. tblFamily_Journal PK MFIDS AutoNumber FID Long Increment of 1 FJID Text (Unique ID) FN LN Open_Date tblFamily_Journal_Additional PK IDS AutoNumber FK FJID FJACID Text (Unique ID) ContactID Text (Unique ID) FN LN Relationship tblFamily_Journal_Emergency PK IDS AutoNumber FK FJID FJECID Text (Unique ID) ContactID Text (Unique ID) FN LN Relationship tblFamily_Journal_Primary PK IDS AutoNumber FK FJID FJPCID Text (Unique ID) ContactID Text (Unique ID) FN LN Relationship tblPersonal_Journal PK IDS AutoNumber FK FJID CNID Interger (Used to create the PJID) PJID Text (Uniquie ID) ContactID Text (Unique ID) FN LN Primary Boolean etc. tblFamily_Journal_Addresses PK IDS AutoNumber FK FJID FJADID Text (Unique ID) Address1 Address2 City State ZipCode Type Date_Add Current Boolean tblFamily_Journal_Phones PK IDS AutoNumber FK FJID FJPNID Text (Unique ID) Phone Type Date_Add Current Boolean Again there needs to be One Primary Contact either a client in the Personal Journal Table or a external individual with FN and LN entered in the Primary Table. How do I connect the address table and phone table? Should I create another table that holds the addressID and ContactID and link them that way, or is there a better way? -TFTH Bryan |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.