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 |
#1
|
|||
|
|||
Normalisation question
This isn't a specific problem I'm trying to crack, but I'd appreciate
some general advice on something that's puzzling me. Am I correct in thinking that, strictly speaking, it's wrong to have a tblContacts with ContactID, FirstName, LastName, AddressLine1 and so forth, as does the Contact Management database that ships with Access? Since, I reason, someone can have more than one address and several people can share an address, shouldn't I have one tblPeople (PersonID, Name, Date of Birth, and other strictly personal attributes), a tblAddresses and a tblPeopleAddresses containing PersonID and AddressID to join the two? Presumably, come to think about it, tblPeopleAddresses should also, to be useful, contain a third field pointing to a look-up tblAddressType (home, business, weekend address or whatever). Similarly, should not both phone numbers and email addresses have their own tables and linking tables, on the grounds that one person can have an indeterminate number of both phone numbers and email addresses? If I am correct,is it common practice to design databases this way? Steve |
#2
|
|||
|
|||
Normalisation question
Stephen Glynn wrote:
This isn't a specific problem I'm trying to crack, but I'd appreciate some general advice on something that's puzzling me. Am I correct in thinking that, strictly speaking, it's wrong to have a tblContacts with ContactID, FirstName, LastName, AddressLine1 and so forth, as does the Contact Management database that ships with Access? Since, I reason, someone can have more than one address and several people can share an address, shouldn't I have one tblPeople (PersonID, Name, Date of Birth, and other strictly personal attributes), a tblAddresses and a tblPeopleAddresses containing PersonID and AddressID to join the two? Presumably, come to think about it, tblPeopleAddresses should also, to be useful, contain a third field pointing to a look-up tblAddressType (home, business, weekend address or whatever). Similarly, should not both phone numbers and email addresses have their own tables and linking tables, on the grounds that one person can have an indeterminate number of both phone numbers and email addresses? If I am correct,is it common practice to design databases this way? Steve I don't know the right or wrong, but when it comes to data for people, I do like a couple of tables with one for just addresses. I like this method because it is easier to have save multiple address. I also use lookup tables for State and Country information. The Address lookup table is a good idea too in my opinion though I've never used one. -- Joe Cilinceon |
#3
|
|||
|
|||
Normalisation question
Joe Cilinceon wrote:
Stephen Glynn wrote: This isn't a specific problem I'm trying to crack, but I'd appreciate some general advice on something that's puzzling me. Am I correct in thinking that, strictly speaking, it's wrong to have a tblContacts with ContactID, FirstName, LastName, AddressLine1 and so forth, as does the Contact Management database that ships with Access? Since, I reason, someone can have more than one address and several people can share an address, shouldn't I have one tblPeople (PersonID, Name, Date of Birth, and other strictly personal attributes), a tblAddresses and a tblPeopleAddresses containing PersonID and AddressID to join the two? Presumably, come to think about it, tblPeopleAddresses should also, to be useful, contain a third field pointing to a look-up tblAddressType (home, business, weekend address or whatever). Similarly, should not both phone numbers and email addresses have their own tables and linking tables, on the grounds that one person can have an indeterminate number of both phone numbers and email addresses? If I am correct,is it common practice to design databases this way? Steve I don't know the right or wrong, but when it comes to data for people, I do like a couple of tables with one for just addresses. I like this method because it is easier to have save multiple address. I also use lookup tables for State and Country information. The Address lookup table is a good idea too in my opinion though I've never used one. Sometimes I use one, sometimes the other. For a mailing list, old addresses are useless (they would waste postage if used, for example) and need not be kept. Since I keep only one address for a person, that can just as easily stay in the same record as the person's name. If I need (or think I might need) multiple addresses, I put them into a separate Table. Or, if I keep track of multiple people at one address, I invert the relationship -- an [Addresses] or [Households] Table to which I can link several [Persons]. If you think you might need to track BOTH several persons in one household AND several addresses for one person, you'd probably need to establish a many-to-many relationship, including a linking Table in which each record associates some human being with some address. But much of the time, that's a bit more complex than what's needed. And even if you know that there might be a couple of cases where two persons in your list share an address, but it's pretty exceptional for that to happen, you might choose to store the address fields with the names, and duplicate the address in those exceptional cases. It would involve extra maintenance, perhaps, assuming that if one person moves the other one automatically does too, but you'd save yourself the trouble of maintaining an additional Table. -- Vincent Johns Please feel free to quote anything I say here. |
#4
|
|||
|
|||
Normalisation question
On Sat, 26 Nov 2005 23:40:49 GMT, Stephen Glynn
wrote: This isn't a specific problem I'm trying to crack, but I'd appreciate some general advice on something that's puzzling me. Am I correct in thinking that, strictly speaking, it's wrong to have a tblContacts with ContactID, FirstName, LastName, AddressLine1 and so forth, as does the Contact Management database that ships with Access? Sometimes... and sometimes it's fine. Since, I reason, someone can have more than one address and several people can share an address, shouldn't I have one tblPeople (PersonID, Name, Date of Birth, and other strictly personal attributes), a tblAddresses and a tblPeopleAddresses containing PersonID and AddressID to join the two? Presumably, come to think about it, tblPeopleAddresses should also, to be useful, contain a third field pointing to a look-up tblAddressType (home, business, weekend address or whatever). If (for your application, for a good business reason) you need to track multiple addresses, you're absolutely right. However, for many uses (for instance a customer table) you might only need to know a mailing address; you don't need to know about the customer's vacation home in Bermuda or their villa in Sicily, it would just make you feel bad g... Similarly, should not both phone numbers and email addresses have their own tables and linking tables, on the grounds that one person can have an indeterminate number of both phone numbers and email addresses? If I am correct,is it common practice to design databases this way? I've done both, but the multitable approach is in fact pretty common; it's just more work, so don't use it unless you have a need to do so. John W. Vinson[MVP] |
#5
|
|||
|
|||
Normalisation question
it's not wrong if only one address is relevant to a contact in whatever
business process you're modeling. remember that any template database is a generic model, and can't be expected to support business processes that deviate from that generic "standard". the other setups you describe are appropriate, when they fully support whatever business process you're building them for. that is why process analysis, as a part of data modeling, is the vital first step in building a custom database. hth "Stephen Glynn" wrote in message ... This isn't a specific problem I'm trying to crack, but I'd appreciate some general advice on something that's puzzling me. Am I correct in thinking that, strictly speaking, it's wrong to have a tblContacts with ContactID, FirstName, LastName, AddressLine1 and so forth, as does the Contact Management database that ships with Access? Since, I reason, someone can have more than one address and several people can share an address, shouldn't I have one tblPeople (PersonID, Name, Date of Birth, and other strictly personal attributes), a tblAddresses and a tblPeopleAddresses containing PersonID and AddressID to join the two? Presumably, come to think about it, tblPeopleAddresses should also, to be useful, contain a third field pointing to a look-up tblAddressType (home, business, weekend address or whatever). Similarly, should not both phone numbers and email addresses have their own tables and linking tables, on the grounds that one person can have an indeterminate number of both phone numbers and email addresses? If I am correct,is it common practice to design databases this way? Steve |
#6
|
|||
|
|||
Normalisation question
The classic case for multiple addresses for one person (and I'm sure we've
all encountered this when ordering online) is Delivery address and Billing address. Is this always needed? No, but sometimes it is. As for two persons at the same address, the classic case is bank statements. If I have a bank account in my name and my wife has an account in her name, the bank better have the ability send out a statement to each of us in separate envelopes. These are some of the possibilities you need to ponder when designing your application. David "Stephen Glynn" wrote: This isn't a specific problem I'm trying to crack, but I'd appreciate some general advice on something that's puzzling me. Am I correct in thinking that, strictly speaking, it's wrong to have a tblContacts with ContactID, FirstName, LastName, AddressLine1 and so forth, as does the Contact Management database that ships with Access? Since, I reason, someone can have more than one address and several people can share an address, shouldn't I have one tblPeople (PersonID, Name, Date of Birth, and other strictly personal attributes), a tblAddresses and a tblPeopleAddresses containing PersonID and AddressID to join the two? Presumably, come to think about it, tblPeopleAddresses should also, to be useful, contain a third field pointing to a look-up tblAddressType (home, business, weekend address or whatever). Similarly, should not both phone numbers and email addresses have their own tables and linking tables, on the grounds that one person can have an indeterminate number of both phone numbers and email addresses? If I am correct,is it common practice to design databases this way? Steve |
#7
|
|||
|
|||
Normalisation question
That's three main tables, isn't it? Customers, accounts and addresses,
all liked in a joining table with AccountID, CustomerID and AddressID. An account can belong to one or more people, people can own, individually or jointly, one or more accounts, and people can also want some of their statements to go to their home address and some to their business address(es). Steve mscertified wrote: The classic case for multiple addresses for one person (and I'm sure we've all encountered this when ordering online) is Delivery address and Billing address. Is this always needed? No, but sometimes it is. As for two persons at the same address, the classic case is bank statements. If I have a bank account in my name and my wife has an account in her name, the bank better have the ability send out a statement to each of us in separate envelopes. These are some of the possibilities you need to ponder when designing your application. David "Stephen Glynn" wrote: This isn't a specific problem I'm trying to crack, but I'd appreciate some general advice on something that's puzzling me. Am I correct in thinking that, strictly speaking, it's wrong to have a tblContacts with ContactID, FirstName, LastName, AddressLine1 and so forth, as does the Contact Management database that ships with Access? Since, I reason, someone can have more than one address and several people can share an address, shouldn't I have one tblPeople (PersonID, Name, Date of Birth, and other strictly personal attributes), a tblAddresses and a tblPeopleAddresses containing PersonID and AddressID to join the two? Presumably, come to think about it, tblPeopleAddresses should also, to be useful, contain a third field pointing to a look-up tblAddressType (home, business, weekend address or whatever). Similarly, should not both phone numbers and email addresses have their own tables and linking tables, on the grounds that one person can have an indeterminate number of both phone numbers and email addresses? If I am correct,is it common practice to design databases this way? Steve |
#8
|
|||
|
|||
Normalisation question
Stephen Glynn wrote:
That's three main tables, isn't it? Customers, accounts and addresses, all liked in a joining table with AccountID, CustomerID and AddressID. An account can belong to one or more people, people can own, individually or jointly, one or more accounts, and people can also want some of their statements to go to their home address and some to their business address(es). Steve You could use three main Tables for that, as you suggest, but you could also easily justify combining the [Customers] Table with the [addresses] Table, with each record in the [Customers] Table including the address fields. Unless you typically have 2 or more [Customers] living at the same place, and they typically all move to the same new address at the same time, it's probably easier to combine them. OTOH, for a church directory that lists parents and children, you'd probably want to link all members of one family to one [addresses] record. There are probably intermediate situations, where neither system is obviously better. What you need to try to determine is how much work (in either case) it will be to make changes and be sure they're consistent, and then choose the method that you expect will minimize the trouble you'll have to go to. -- Vincent Johns Please feel free to quote anything I say here. |
#9
|
|||
|
|||
Normalisation question
Vincent Johns wrote:
Stephen Glynn wrote: That's three main tables, isn't it? Customers, accounts and addresses, all liked in a joining table with AccountID, CustomerID and AddressID. An account can belong to one or more people, people can own, individually or jointly, one or more accounts, and people can also want some of their statements to go to their home address and some to their business address(es). Steve You could use three main Tables for that, as you suggest, but you could also easily justify combining the [Customers] Table with the [addresses] Table, with each record in the [Customers] Table including the address fields. Unless you typically have 2 or more [Customers] living at the same place, and they typically all move to the same new address at the same time, it's probably easier to combine them. OTOH, for a church directory that lists parents and children, you'd probably want to link all members of one family to one [addresses] record. There are probably intermediate situations, where neither system is obviously better. What you need to try to determine is how much work (in either case) it will be to make changes and be sure they're consistent, and then choose the method that you expect will minimize the trouble you'll have to go to. I don't quite follow your point about 'unless you typically have 2 or more [Customers] living at the same place, and they typically all move to the same new address at the same time, it's probably easier to combine them'. I'd have thought the opposite was the case. Take, for example, a student at university who lives on campus during term time and with his parents during the vacation. He'll want his bank statements and credit card bills to go to wherever he's living at the time. With the three table model, all the operator needs to do is to change the AddressID field in the joining table (either with an update query or manually, using a list box on a form) for the accounts with his CustomerID each time he tells them to. Far simpler, I'd have thought, than having to change all his address fields in a table each time he moves. Steve |
#10
|
|||
|
|||
Normalisation question
Stephen Glynn wrote:
Vincent Johns wrote: [...] I don't quite follow your point about 'unless you typically have 2 or more [Customers] living at the same place, and they typically all move to the same new address at the same time, it's probably easier to combine them'. I'd have thought the opposite was the case. What I meant was that one address for several persons is a good model for families all of whose members share an address and change addresses at the same time. If the persons are merely roommates, and they are likely to move to different places when they move, you probably don't gain much by having one address apply to both -- when one moves, you'll have to break at least one of the links instead of just updating a data field. Take, for example, a student at university who lives on campus during term time and with his parents during the vacation. He'll want his bank statements and credit card bills to go to wherever he's living at the time. With the three table model, all the operator needs to do is to change the AddressID field in the joining table (either with an update query or manually, using a list box on a form) for the accounts with his CustomerID each time he tells them to. Far simpler, I'd have thought, than having to change all his address fields in a table each time he moves. Steve In the case of the college student, assuming the address formats are similar, it probably would make sense to store the addresses for home, dormitory, on-campus job, and off-campus job in an [addresses] Table, with a field indicating whose address it is and a field indicating which type of address it is. Where it would be even less clear would be if the data are phone numbers or email addresses, which would be even shorter than mailing addresses, and maybe not as likely to be shared by several persons. Storing the entire phone number in a suitable field (one field for home, another for business hours, another for cell phone, another for emergency) might be easier than keeping a separate Table in which each record contains a field identifying the person, one containing the phone number, and one containing the type of number it is. Either design would work, but which is better you might have to determine through experience. For example, you might count how many types of phone numbers (on average) each person in your list has, or how often they're updated, or you might need to analyze what types of Queries you most often run against your lists. Some of those answers you may not be able to determine before the fact. -- Vincent Johns Please feel free to quote anything I say here. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Database design question | Stephen Glynn | New Users | 4 | November 24th, 2005 03:28 PM |
Question about reducing number of tables in a database | tlyczko | Database Design | 0 | October 27th, 2005 04:15 PM |
x-post from previous question | justin | Using Forms | 2 | April 13th, 2005 09:48 PM |
A report design question | Al | Setting Up & Running Reports | 3 | March 11th, 2005 09:41 PM |
database design question | e-mid | Database Design | 9 | June 16th, 2004 09:42 PM |