Hi there,

I am having problem with dataase design stage.

This is similar to the for example amazon customers details and customer contact address and delivery address.

I am going to collect customer details and also customer contact address (or billing address) and also delivery address.

So the assumption is that the each customer has got one contact address and zero or 1 or more delivery addresses.

So I was wondering how can I make this ERD since in some situations the customers contact address and delivery address are the same and in another situations the customers contact address and delivery address are different and we may have more than one delivery addresses.

I have been thinking my different solutions for this scenarios but all the time I end up with duplication or null values.

I should mention that the rest of entities are related to the delivery processes therefore I need to have the delivery address in order to use it for the delivery process. So if I have got three different tables for customer and contact address and delivery address, in the situation that the contact address and delivery address are the same I can choose to repeat the address in both tables which is the repeating of data or I can choose to enter the address into the contact address table and no values for delivery address table however this will cause problem with the rest of delivery process since they will have no values for the delivery address to continue the delivery process.

Thanks very much for your advices

my advice: for the purposes of the delivery processes, you need a view

let the view decide which address to return for the customer

this frees you up to design the addresses in a manner that makes sense for addresses

rest assured that no matter which way you decide to store the addresses, you can then write a view which will be efficient and simple and will satisfy whatever the delivery processes need

thanks for your advice.

i was just wondering whether it is possible to create these three tables and draw the ERD for them without using view. i was wondering whether i can apply different apply different type of relationships such as subtype and supertype or recursive and etc to create a simple relationship which allows the mentioned senario.

Many Thanks
ctxms

yes you can create tables and draw an ERD for them without a view

There are many alternative designs.

Think further about what your goal is and the rules you want to observe. This will guide your design. Don't worry about what what modeling devices you will use as your rules will dictate that.