Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: Looking for some feedback on the stability of this database schema

    Hi,
    Can you please give me some feedback on this database schema. I've included a screenshot of the tables and their relationships. I'm planning on using it for an ecommerce site. Some feedback on its stability and possible errors that I might have made would be useful.
    Thanks
    Attached Thumbnails Attached Thumbnails myERD.jpg  

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The best way to test your design is to make up a story and see if your model can handle it.

    Lets see what I can come up with:
    You have a stock of USB sticks, bought from company A.
    Now company B contacts you, offering them to you for half the price you bought them from company A.
    People come at your site, searching for things to buy. How will they find USB-sticks? From "Computer supplies", to "peripherals" to "USB-sticks"?
    A customer complains his package was never delivered.
    ...

    I think your model is a good starting point, but not production ready:
    - a product has only 1 supplier
    • I see a many-to-many relation here

    - a product only belongs to 1 category
    • I would provide layers in your categories, like the (sub)Category "Peripherals" belongs to the Category "Computer supplies". Here you are defining how customers will be able to find stuff on your site.
    • you could also consider making a product belong to multiple categories


    Play around, put your imagination at work. What will you do if a customer doesn't pay, ..., a product gets broken, lost, ...
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2009
    Posts
    2
    Hi, thanks for your input. I have one question however. Can you be more clear on the 'many to many relationship' between product and supplier? True, many suppliers can have the same product, but wouldn't the foreign key 'productID' in the suppliers table clear that up?

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    many suppliers can have the same product, but wouldn't the foreign key 'productID' in the suppliers table clear that up?
    Check your model, there is no productID in the Suppliers table (and rightly so).

    -- Something as an aside: tables are never named in plural form. Tables don't get named like Suppliers, Products, ... but Supplier, Product, ... --

    Try to figure out the implications of having 'productID' in the Supplier table. How many products could a Supplier sell?

    In your model the Product table holds the 'SupplierID'. How many Suppliers could sell that Product?

    Ever heard of a cross table or junction table? Its a table that holds two (or more) primary keys. Here you will need a Supplier_Product table

    Code:
    CREATE TABLE Supplier_Product (
    SupplierId   bigint  not null,
    ProductId   bigint  not null,
    CONSTRAINT pk_Supplier_Product PRIMARY KEY (SupplierId, ProductId)
    )
    In that table you can store all the suppliers that can sell a certain product and all the all the products that a certain supplier sells.

    Another thing I would certainly add to your model is history information.
    - The prices of the products will likely change over time. Make sure you can find out later what the pricing was of USB-stick XYZ on June 12th 2009. Just in case someone complains about the pricing three months later.
    - At the same time you could solve the problem of doing a promotion on a few products during a limited time, like 1 week. You must be able to state that product A will cost $ 4.50 from 2009-06-01 till 2009-06-07 and back to its normal price of $ 9.99 from 2009-06-08 on.

    See what you can come up with and post your new model.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Wim
    -- Something as an aside: tables are never named in plural form. Tables don't get named like Suppliers, Products, ... but Supplier, Product, ... --
    sorry, but this is wrong, wrong, wrong

    programmers (~ptui~) tend to use singular table names

    properly trained database developers always use plural table names

    that is all

    we now return you to your regularly scheduled thread

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    programmers (~ptui~) tend to use singular table names

    properly trained database developers always use plural table names
    I did the test and browsed the database design books I have on my shelf.
    - Those addressing SQL Server used plural table names - though I spotted one instance of 'CREATE TABLE foobar' instead of 'CREATE TABLE foobars'. I guess no-one is perfect - .
    - The other books, database independent and DB2-specific, used singular table names, except for one that also used the plural names.

    Ruby on Rails programmers will most likely use the plural names too, as in Rails, a model is automatically mapped to a table who's name is the plural of the model's class name. And a smart developer prefers to swim with the flow of his framework, not against.

    I think we gathered enough conclusive evidence to safely state that:
    1. Only properly trained database developers write database design books tended towards SQL Server.
    2. Ruby on Rails programmers are better than ordinary programmers (~ptui~), if they swim with the flow.

    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the idea behind using plural table names is that it constantly draws your attention to the contents -- the rows -- rather than the container -- the table

    in a relational database, it is the rows that matter, and this concept is closely tied to the idea of uniqueness and identity of the individual rows in the table

    a table named products suggests that there is more than one product, and each product is different

    mull it over for a while, and i'm sure you will see the subtlety of this small but significant convention



    the only people who care about tables as tables are (ptui ptui ptui) DBAs
    Last edited by r937; 06-04-09 at 23:52.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    mull it over for a while, and i'm sure you will see the subtlety of this small but significant convention
    I have seen THE LIGHT !!!!!

    I believe in standards, as they can make your work easier over time. Most of the time it is not important what the standard is (if you use 'foobar' or 'foobars', 'id_product' or 'ProductId'), but the most important is the fact that there is a standard and it is followed by everyone in your team/dept./.... Then stick to it until time proves you should use another one. Then stick to that one until ...

    Just my 2 cts.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Jul 2006
    Posts
    22
    That model corresponds to Northwind demo database; so I guess is designed by Microsoft.

    The only extra tables i can see are RefPaymentMethod and CustPaymentMethod. But if you want to know which payment method was used in each order then the payment details should be within Orders table and associate PaymentMethods table with Orders table.
    Last edited by carloco; 06-08-09 at 17:52.

Posting Permissions

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