database design question

hi, I'm stuck with database design, and come to know of this forum, hope the experts here can provide me an advice.

I'm trying to design a database, to store the following type of information.

- Person (name, identity no...)
- Phone (phone type (mobile, residential, fax), number
- Address (street, country...)
- Person and phone relationship. A person can have 0 or more Phone, and the relationship can be user or subscriber.
- Person and address relationship. A person can live in 0 or more address, and the relationship can be occupants, owner...
- Phone and address relationship. A phone can be have 0 or more address, and the relationshp is installation address, billing address

My design forms a circle, person - person_phone- phone - phoneAddress - Address - personAddress (-back to person). Is this a bad design, if it is, any solution to overcome?I see nothing wrong with the circular design, since the intermediary table in your many-to-many relationships will prevent relational integrity issues cascading around your design.

A few questions, though...

Can a phone have more than one User?
Can a phone have more than one Subscriber?
Can a phone have more than one Installation Address?
Can a phone have more than one Billing Address?
Are these the only two types of Phone addresses, or are there potentially an unlimited number of address types for phones?

blindman|||thanks blindman for the assurance... coz I'm rather confuse, as I'm not sure will there be any hidden problems with this type of design.

1. Can a phone have more than one User? yes
2. Can a phone have more than one Subscriber? at any one time, no... but the number can be re-use (eg, the first subscriber unsubscribe, and another one subscribe to it)
3. Can a phone have more than one Installation Address? similar to 2
4. Can a phone have more than one Billing Address? similar to 2
5. Are these the only two types of Phone addresses, or are there potentially an unlimited number of address types for phones? might have more, but not identified as of now.

will this question affect my design?|||There are some alternative designs for this type of modeling, but it looks to me like you are going to require all the flexibility in the circular schema you have. That just means you will need to store more data-integrity logic in the procedures you write for inserting, updating, and deleting records.

See if anybody else has comments on this over the weekend. Otherwise, go with it.

blindman

Database design problem

I'm trying to built a database for ecommerce and I'm stuck with this problem.

I'll use a grocery store for the example.

First here's the tables.

table product_prd
id_prd
name_prd
description_prd

A product may have different "sizes" like a bag of chips. The table product_prd is linked (one-to-many) to product_size_pds

table product_size_pds
id_pds
idprd_pds //Foreign Key - product_prd.id_prd
name_pds //this contains the name of the size (ex. small,medium, large)
price_pds
weight_pds
lenght_pds
height_pds

I thinks there's a problem with my structure if the product as only one size like a camping chair.

I just can't leave the name_pds field empty or repeat the name from name_prd. It seems bad practice.

Is there a structure that could hold single size product and multiple size products?

Hope this makes sense.

Thanks

Quote:

Originally Posted by ZoeNet

I'm trying to built a database for ecommerce and I'm stuck with this problem.

I'll use a grocery store for the example.

First here's the tables.

table product_prd
id_prd
name_prd
description_prd

A product may have different "sizes" like a bag of chips. The table product_prd is linked (one-to-many) to product_size_pds

table product_size_pds
id_pds
idprd_pds //Foreign Key - product_prd.id_prd
name_pds //this contains the name of the size (ex. small,medium, large)
price_pds
weight_pds
lenght_pds
height_pds

I thinks there's a problem with my structure if the product as only one size like a camping chair.

I just can't leave the name_pds field empty or repeat the name from name_prd. It seems bad practice.

Is there a structure that could hold single size product and multiple size products?

Hope this makes sense.

Thanks

What you might need in your design is transform the product_size_pds table into a mapping table and define the product sizes in a separate table named size:

table product
id_prd //Primary Key (Identity)
name_prd
price_prd
description_prd

table map_product2size
id_prd2size //Primary Key (Identity)
id_prd //Foreign Key from product_prd table
id_size //Foreign Key from size table

table size
id_size //Primary Key (Identity)
name_size //this contains the name of the size (ex. small,medium, large)
weight_size
lenght_size
height_size|||

Quote:

Originally Posted by davef

What you might need in your design is transform the product_size_pds table into a mapping table and define the product sizes in a separate table named size:

table product
id_prd //Primary Key (Identity)
name_prd
price_prd
description_prd

table map_product2size
id_prd2size //Primary Key (Identity)
id_prd //Foreign Key from product_prd table
id_size //Foreign Key from size table

table size
id_size //Primary Key (Identity)
name_size //this contains the name of the size (ex. small,medium, large)
weight_size
lenght_size
height_size

If I take a single size product (the camping chair) with your structure, I still need to enter a size to the product witch I don't think is applicable for a single size product. And for the price it should be in the table size because the price varies with the different size of the product.|||

Quote:

Originally Posted by ZoeNet

If I take a single size product (the camping chair) with your structure, I still need to enter a size to the product witch I don't think is applicable for a single size product. And for the price it should be in the table size because the price varies with the different size of the product.

If you define a size, can it be applied to different products? If yes, then you move the price column to the mapping table such that the product price is defined by the combination of product id and size id. And it's fine to have a product with a single size - it just happens to be a one-to-one relationship inside the mapping table.|||

Quote:

Originally Posted by davef

If you define a size, can it be applied to different products? If yes, then you move the price column to the mapping table such that the product price is defined by the combination of product id and size id. And it's fine to have a product with a single size - it just happens to be a one-to-one relationship inside the mapping table.

I'll try that thanks