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

    Question Unanswered: Adding a table that is parent to a complex set of tables

    Hi Everyone,

    I am really stuck on this one.

    I have a database design for an online store with some specific requirements with regards to how discounts and sales work. Attached is a screenshot of the DBML file generated by MS VWD Express 2008.

    I want to modify the database so that there can be multiple stores. Obviously I create a 'Stores' table. My question is how do I relate the Stores table to all the other tables?

    Any other suggestions on how to improve the design are also welcome.

    Thank you in advance.

    Jeff Paetkau

    PS: Using MSSQL Express 2008
    Attached Thumbnails Attached Thumbnails DB-Diagram.png  

  2. #2
    Join Date
    Mar 2009
    Posts
    349
    that's not complex. looks like homework, but it seems to me that a Store could have a sale

  3. #3
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    My guess would be add a store table with the store information.

    Then add a store column as a foreign key reference to the store table in the Product table (so you can manage inventory quantities in separate stores) and a store column as a foreign key reference to the store table int he Sale table (so you can link a sale to a specific store)

    The store in the product table would become part of the primary key along with product ID.

    I would suggest normalizing that a bit more though and splitting the product table into two tables - product and product_store, where product contains the general information about the product (name, description, category etc etc) and product_store contains the store specific information against the product (product id, store id, quantity in stock, quantity on back-order with suppliers etc) - In this case the product_store table would have the store key and not the product table.

  4. #4
    Join Date
    Jun 2009
    Posts
    2
    Thanks EngadaSQL,

    That was my first approach except with the Categories table. However, that doesn't work because several of the other tables are also related to the store (Such as Sales and taxes etc.)

    I could at a Store_ID column to those tables too. Unfortunately, then it would be possible to have a category/product in one store linked to a tax in a different store! That can't be right.

    My guess is it has something to do with adding a store_id column to every table and making that part of the table key. I just don't know enough about database design to know for sure or exactly how to do that.

  5. #5
    Join Date
    Jul 2006
    Posts
    22
    My approach generally goes by "understanding" the key event(s) of the system. In this case i guess is the sale.
    So i would ask "where" the sale occurs? the answer is: in the store. So establish a relation between sales and stores.
    Also ask "what" is sold? The answer: a product, so i would associate sales with products not with categories (nor category_sale).

    Also talking about connectivity/cardinality if the head of the arrow means the "one" side then a lot of relations seem (very) wrong to me (or vicevers)

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    It all depends on what you need. If you are writing a database to be used by 1 big company, that has multiple stores, it is the easiest. All products then belong to the same category, have the same image, tax, discount category, ...

    If you plan to make 1 data model to serve completely unrelated shops with their own product-images, discount-categories, tax, ... you will have to make a more profound remodelling.
    Unfortunately, then it would be possible to have a category/product in one store linked to a tax in a different store! ...
    My guess is it has something to do with adding a store_id column to every table and making that part of the table key.
    Good thinking! It is a way to let the database protect its integrity.
    But in practice I would not implement it that way. It has serious drawbacks when you have to maintain your database. A PK can become quite long as it may consist of many FK's. Just go on as you already did in your model, give each table an ID as PK and let the FK's be no part of the PK.

    I agree with the remarks of Carloco.
    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
    Jun 2004
    Location
    Long Island
    Posts
    696
    The old junction table.

Posting Permissions

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