Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    7

    Please help me with data design!!!!!!!!!!!!

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Posts
    7
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes you can create tables and draw an ERD for them without a view
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •