I am a newbie in database. Our course project is to make a online book store. We created a table called "Customer". As required, there should be "Shipping address, including city, state, zip" and "Billing Address,including city, state, zip". If we add all those fields into one table, it will be data redundancy if those two address are the same for customers.How do you deal with this situation?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
question on table design
- Thread starter oracler
- Start date
Usally, in that situation you do not need to worry about the redundancy since it will be rare to have two people at the same address. I have seen on occasion someone making a city table however. I personally wouldn't do it, but maybe someone has a good reason why it should be done. If you do choose to split City into another table, you would then replace the city field with a cityID field. This field would be linked to the CityID field in the City table. Therefore, instead of the repeating of cities, there would be an id number that represented the city in the other table. In the relationships window, you would need to link these two tables by the CityID field. Make sure they are both the same data type and size. If you use auto number in the city table for the id, then use number as the datatype in the customer table (for CityID), the size for both will be long integer. Does this help at all? Dawn
Require a billing address. The shipping address only gets completed if it differs from the billing info.
In later processing, if there is nothing in the shipping address field, use the corresponding billing address field.
Some procedural or code enforced rule may be used so that if any shipping info is entered, then all shipping info must be completed rather than just the differing info.
Dave
In later processing, if there is nothing in the shipping address field, use the corresponding billing address field.
Some procedural or code enforced rule may be used so that if any shipping info is entered, then all shipping info must be completed rather than just the differing info.
Dave
I was thinking redundancy with records, not within the same record. Sorry. Thanks nuefarth- Thread starter
- #5
Thank you guys.
One of our teammate suggest to add only one set of address(including street, city, zip, and something like that) in the table, and add an address type- "0" indicates "billing address" and "1" indicates "shipping address". If a person fills in two addresses, then there will be two records in the table.What do you think about this suggestion? The problem about is that in the data entry form, if a person have entered two addresses, he have to enter the same general information (name, phone, email) two times. I am wondering if we can put all those information in one page of form, say if the two addresses are the same, we can just click some button or option box, and the form will automatically add two records into the table, which only differ in Address type. How to do that? Thanks
One of our teammate suggest to add only one set of address(including street, city, zip, and something like that) in the table, and add an address type- "0" indicates "billing address" and "1" indicates "shipping address". If a person fills in two addresses, then there will be two records in the table.What do you think about this suggestion? The problem about is that in the data entry form, if a person have entered two addresses, he have to enter the same general information (name, phone, email) two times. I am wondering if we can put all those information in one page of form, say if the two addresses are the same, we can just click some button or option box, and the form will automatically add two records into the table, which only differ in Address type. How to do that? Thanks
- Thread starter
- #7
Thanks. Maquis. Another question: When I have two subforms, I want to achieve this: I don't display the "address type" in the form. When all the required fields are filled in in the "Billing address" subform, the value of "address type" field in the table will be automatically set to "0", and to "1" when the "shipping address" subform is filled. However, if both of them are filled out, I want the form add two records to the table, with same personal information, except for the address. How to do that?
Have you thought about having a separate table like:
tblCustAddresses
CustID (Foreign key to your main table)
AddressType (Billing or Shipping)
AddressLine1
AddressLine2
...
This way, you don't double the customer name, etc, just because they have multiple addresses.
Just a thought... Terry M. Hoey
tblCustAddresses
CustID (Foreign key to your main table)
AddressType (Billing or Shipping)
AddressLine1
AddressLine2
...
This way, you don't double the customer name, etc, just because they have multiple addresses.
Just a thought... Terry M. Hoey
- Thread starter
- #9
Oh, man. That is exactly what we did. I misread the table. However, the problem still there:
I don't display the "address type" in the form. When all the required fields are filled in in the "Billing address" subform, the value of "address type" field in the table will be automatically set to "0", and to "1" when the "shipping address" subform is filled. However, if both of them are filled out, I want the form add two records to the "address" table, with same Cus_ID. How to do that?
I don't display the "address type" in the form. When all the required fields are filled in in the "Billing address" subform, the value of "address type" field in the table will be automatically set to "0", and to "1" when the "shipping address" subform is filled. However, if both of them are filled out, I want the form add two records to the "address" table, with same Cus_ID. How to do that?
I believe Access will do that automatically. Just make sure the recordsource for the sub-forms is set to the appropiate table. (The Primary key is a combination of Cust Id and Address Type, right?)
You can have invisible text boxes on the subform bound to Cust Id and address type. That way the sub-form automatically knows how to find and save the appropiate records.
You may run into the problem though, of Access saving empty shipping addresses when you don't fill them in, but since this is schoolwork for ya, I'll let you figure that one out yourself. (Basically a fancy way of saying I don't know the solution off the top of my head.) Maq B-)
<insert witty signature here>
You can have invisible text boxes on the subform bound to Cust Id and address type. That way the sub-form automatically knows how to find and save the appropiate records.
You may run into the problem though, of Access saving empty shipping addresses when you don't fill them in, but since this is schoolwork for ya, I'll let you figure that one out yourself. (Basically a fancy way of saying I don't know the solution off the top of my head.) Maq B-)
<insert witty signature here>
- Status
Similar threads
- Question
- Locked
- Question
- Locked
- Question