Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2012
    Posts
    4

    db schema sounding board

    Hi there,

    A friend runs a custom made webshop. The webshop is built by a webdeveloping company and is running since a year. There are still many bugs. Especially with numeric values and EAN codes (13 character numeric codes). There is no budget for switching over to an other firm and the current firm says the system is ready.

    I've studied database design about twenty years ago (but never used it since) and was asked to review the webshop.

    I've reconstructed the database schema from hardcopy and was shocked by the design. The same (type of) data is stored in multiple tables and is differently declared in different tables. I now need a sounding board so I can motivate within a report what I think about the database design. Can you please review the design and comment.

    Thanks
    Attached Thumbnails Attached Thumbnails schema.v1.png  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    looks okay to me

    what specific problems do you have with the design?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2012
    Posts
    4

    db schema sounding board

    Database is not normalised and (thus) contains a lot of redundance data.
    E.g. address is in ws_extra_sites, ws_suppliers, ws_customers and ws_orders.
    There is calculated data (price_inc, price_tax) in ws_orders_products and ws_products.

    Maybe more ???
    Last edited by questo; 04-07-12 at 18:14.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    those examples you gave do ~not~ represent unnormalized data

    yes, a customer has an address, and yes, a supplier has an address

    that's perfectly okay, and does ~not~ require fixing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2012
    Posts
    4
    Thanks for your thoughts.
    I can imagine that there is a full address in ws_orders. After delivering that address will not change and is historical data.
    But a supplier can also be a customer in which case the personal data of the person and his or hers address is redundant. In this case there are a lot of suppliers also customer.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by questo View Post
    In this case there are a lot of suppliers also customer.
    okay, fine

    do a search for supertype/subtype

    then suppliers and customers become subtype tables, and the supertype, perhaps called persons, would carry the address

    if you feel that making this change is worth the effort, go for it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2012
    Location
    near Dallas, Tx
    Posts
    7

    Normalized Data

    Supplier Address and customer address are different fields. IMHO, you can over normalize and make a mess of things. If you are always pulling the address when you read a customer/supplier, that results in two row fetches not one. I have no problem having duplicate data in the system for different entities. If the supplier moves his receiving to a different address, it's a lot simpler than trying to split up address records.

    Normalization's goal is to make the database efficient, not to remove any chance of duplicate data.

    I have a web based system and watch the execution times closely. I have taken dog reports that ran in minutes, optimized the SQL code, and the report dropped to under a second. remember INDEXES ARE YOUR FRIEND

    I saw an over normalized database with one address table for various things, ran like a three legged dog.

    rwt

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by russthom View Post
    I have no problem having duplicate data in the system for different entities.
    i completely agree, because it is actually different data

    how many entities in a typical schema have a "name" column?

    there's product name, vendor name, store name, city name, discount name...

    none of these are duplicate data

    neither is supplier address and customer address

    here's a good rule of thumb: does the address by itself mean anything to you, regardless of whether there's a customer or supplier currently occupying that address?

    if the answer is no, then don't set up address as an entity
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2012
    Posts
    4

    Thanks for your contribution.

    So many people so many ways to get to Rome.
    My friend finds it important to to be precise. So there has to be a mechanism which triggers the fact that the customer is also a supplier and vice versa.
    i.e. A product can be sold by several suppliers. Then it comes in handy to know when a supplier is customer with great buying potential.
    Changing a suppliers adress and not changing a 'duplicate' customers address is not neat.
    The fact there is a discussion over this subject makes it important.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by questo View Post
    Changing a suppliers adress and not changing a 'duplicate' customers address is not neat.
    the way to solve this problem is with a supertype/subtype structure, where you tie the address to the supertype, e.g. "person" or "contact"

    then customer and supplier are subtypes, and a person can be both
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

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