Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Sep 2011
    Posts
    39

    Unanswered: My database Design/Structure

    Hey guys. Does everything look fine.Is it 'normalized'?

    http://img31.imageshack.us/img31/319/nellysdatabase.png

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    a few questions:

    1) Why do we have the same attributes in Orders and Customer information?

    2) Assumption: Item photo is the absolute path to the item image and not the image.
    do we have scenarios where an item can have mutiple photos, all of which are required to be shown in the application?

    3) # remaining can be derieved from the Stock and rented out, this can be removed.

    4) Will there be a scenario where the same product can have different unit price? For example, Products manufactured on September-2011 has 'X' as unit price but the ones manufactured in August-2011 has 'Y' as unit price
    Cheers....

    baburajv

  3. #3
    Join Date
    Sep 2011
    Posts
    39
    1) Hey sorry about that...i mistakenly copied the same columns into the Customer Info table .Here's the updated version
    http://img16.imageshack.us/img16/2782/databasnellys.png

    2) Hey.whats the difference between putting the path and actual image? And Yeah i didnt think about it but it would definitely be a good idea to be able to have different images for one item. Should i create a separate "Images" table? and do a one to many relationship from the Orders table?

    3) Alright

    4) Also something i didnt consider. Would you suggest adding something like "DateOfManufacture" to the inventory table?

    And if it helps this database is meant for an asp.net website...

    and thanks in advance!

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Here are some things I noticed:

    1) Will your shop ever offer promotions? How will those fit in the data model?

    2) You will not have a history of your product prices (same as baburajv's 4)). I would not use the name "DateOfManufacture", unless your are actually manufacturing those products themselves. It could be a misleading name.
    Rather use "StartDate" or something like that. Using a coupled column "EndDate" is a bit more work while programming and making sure it gets its correct value (when another record is added and it gets a value for StartDate), but once done, it makes SELECTs a lot easier to write and faster.

    3) There is a typo in REVIEWS.Review

    4) INVENTORY ORDERS:
    -- Is ID the PK?
    -- There should be a FK to OrderId, not CustomerId. CustomerId is already present in ORDERS

    5) ORDERS.Amount Owed occurs twice

    6) Will you allow an order to be delivered in parts? Or will you stall shipment until all products are in stock?
    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
    Sep 2011
    Posts
    39
    1)Promotions? as in discounts/sales?
    Should I add a "DiscountPrice" column to the Inventory table?

    2)Hey Wim.. you lost me on the stuff about EndDates.which table are you referring to?...can you clarify?

    3) Thanks... typo

    4) Thanks..silly mistake

    5)Thanks..

    6)good question. Thats kinda the purpose of the "Order Status" table. The idea is a customer places an order. And for that specific order, the owner can put several comments into the Order Status table.

    Also forgot to mention its a rental equipment business... rents out glassware, cutlery, tables, table cloths, 'party stuff' for events/weddings/parties...

    Another thing. Is the INVENTORYORDERS table the appropriate place to put the Order Quantity(the quantity of a specific item ordered)?
    or should i create a separate table for order quantity?

  6. #6
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Image storage:
    --------------
    Storing images in tables are not recommended. Please refer Store images in the database

    You can put the images in an application specific folder and store the absolute path of the image(s) in a separate table (ProductImage or so). Also create a suitable folder hierarchy to group images based on category/name/year etc.

    History of prices: Start Date and End Date (Wim's reply)
    ------------------------------------------------------
    Wim suggested storing of Itemprice along with the date range in which the price is applicable. Create a table that stores ProductId, UnitPrice StartDate EndDate

    For example, if an item X (productid 100) has different prices in August-2011 and September-2011, the data will be like

    ProductId, UnitPrice StartDate EndDate
    100 $10 2011-08-01 2011-08-31
    100 $15 2011-09-01 2011-09-30

    Discounts/Promotions (Wim's question)
    ---------------------------------------
    Discounts/Promotions should not be kept in Inventory table since this varies from time to time and It is a good idea to keep this separate.

    Address of customer:
    ----------------------
    I suggest storing this in a separate table with appropriate level of detail such as address, city, state, zip etc

    Orders: Payment details:
    -------------------------
    If you plan to support part payments on orders, store payment related information in a separate table. Make suitable changes to schema ,if you support different payment modes (Cash/ Credit card etc)
    Last edited by baburajv; 09-09-11 at 06:11. Reason: Adding a few points
    Cheers....

    baburajv

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    2)Hey Wim.. you lost me on the stuff about EndDates.which table are you referring to?...can you clarify?
    I think Baburajv's already did a good job.

    In reality, you will not know on beforehand when a product's price will change. So the EndDate will often be unknown. I always use "The End Of Time Date" 9999-12-31 as value, instead of NULL. It makes your queries easier to write.
    WHERE GetDate() BETWEEN StartDate AND EndDate
    Otherwise you will need to use COALESCE().

    The implication is that you will need to update the EndDate of the previous record when you enter a new price.

    ProductId, UnitPrice StartDate EndDate
    100 $10 2011-08-01 9999-12-31

    ProductId, UnitPrice StartDate EndDate
    100 $10 2011-08-01 2011-08-31
    100 $15 2011-09-01 9999-12-31


    Is there any reason why you named the PRODUCTS table INVENTORY? It's PK is ProductId, so it would seem logical to me that the table is also named that way.

    1)Promotions? as in discounts/sales?
    Should I add a "DiscountPrice" column to the Inventory table?
    Give it a thought and post what you came up with.


    Also forgot to mention its a rental equipment business... rents out glassware, cutlery, tables, table cloths, 'party stuff' for events/weddings/parties...
    That is important information. From my experience, stuff is rented for periods. Like : $ 50 for a weekday, $ 80 for two weekdays, $ 100 for 5 weekdays, $ 150 for 7 days, $ 120 for a full weekend, .$ 200 for 2 weeks, ...

    I don't see that in your model. Your model says: renting a table costs $ 15, no matter for how long.

    6)good question. Thats kinda the purpose of the "Order Status" table. The idea is a customer places an order. And for that specific order, the owner can put several comments into the Order Status table.
    Ask yourself the question: when I have delivered part of the products of Order X, what products do I still have to deliver? That information cannot be found.
    Also make sure your system is able to register a deliver of 99 units of product X with an order for 100 units fr that product.

    Address of customer:
    ----------------------
    I suggest storing this in a separate table with appropriate level of detail such as address, city, state, zip etc
    When this information is already in a separate table, adding one DATE column offers you an address history.
    A separate ADDRESS table will make it easy to add a delivery address to your model.

    You should first come up with a list of "situations" your system must be able to handle (renting fees per period, partial deliveries, partial payments, delivery address different from billing address, ...), only then start making the 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

  8. #8
    Join Date
    Sep 2011
    Posts
    39
    Alright guys. I will make all the changes and post later on today.

    One more question:Is InventoryOrders table the appropriate place to put OrderQuantity?

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Is InventoryOrders table the appropriate place to put OrderQuantity?
    Yes.

    How about the situation where rented stuff is missing or broken?

    Really, you should make up a story about how your customer is going to find your website (I suppose you'll have one), is going to search on it, select items, deselect items, inserts units, orders, changes his/her mind, .... , breaks stuff, wants bigger tables once they see the delivered ones are too small or unstable , ... After that, start making up your data model.

    Don't try to create a monster super flexible data model that can cope with whatever bizarre situations that might occur.
    Normally all stuff will be hired for the same period, so you can store the period in the ORDER table.
    Suppose someone wants to rent stuff for the weekend, but wants the tent to stay for another week. You could come up with a solution where you can store the period per ordered item, requiring you or your customer to repeat the same data over and over again for each and every item, or you could make another Order, just for the tent with a longer period.

    Perhaps you will want to offer a package: $150 to rent for one weekend the medium tent, 2 tables, 8 chairs, 8 whine glasses, 8 champagne glasses, 8 dishes, ...

    You first have to make up your mind what you want to offer, only then start designing your data model.

    Have fun.
    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

  10. #10
    Join Date
    Sep 2011
    Posts
    39
    well..I want the OrderStatus(i should probably rename it to OrderDeatails or something) to handle a lot of the specific order details...missing or broken items,Fees/Charges,comments,etc. I'll add those attributes to that table.

    EDITED...
    Last edited by caa5042; 09-09-11 at 19:57.

  11. #11
    Join Date
    Sep 2011
    Posts
    39
    updated ....i tried to take into account everything you guys said. Point out anything that you see wrong...(i will probably use a filestream storage system for the images and i will have a separate images table to store size, dimensions,etc.

    http://img850.imageshack.us/img850/7559/databasef.png
    Last edited by caa5042; 09-12-11 at 01:50.

  12. #12
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Customer - Address
    -------------------

    As per the current schema, a customer can have only one address, since it is stored in customer table. Keeping the address separate will give flexibility to add multiple addressess for the same customer. It will be better to have two tables one for customer details and the other for address, linked by CustomerID

    CustomerAddress
    =============
    CustomerID,
    Address,
    city
    State
    Zip
    ... (any relevant address attributes)
    AddressType ( MailingAddress/Delivery Address) etc

    Think of a suitable PK for this table

    With this, the Order.DeliveryAddress will change accordingly (point to the PK of the relevant address record for this customer)

    ORDER
    ------------

    a) TotalCost is not required since it can be derived from ItemPrice and ItemQuantity

    b) SpecialOrderID:- From the current schema what i understand is that an order can have only one SpecialOrder (and that too for a single product). Please correct me if I am wrong and elaborate on this attribute.

    c) TotAmtPaid :- Since there is a separate table (Payment) to keep track of payments related to the Orders, we do not require this attribute in this table.

    d) TotAmountDue is not required since this value can be derived from TotalCost and TotalAmountPaid

    ORDERDETAILS
    --------------
    a) FEE : Assuming that the use of this attribute is to keep track of the Fines collected for any missing/broken items, I suggest using the "Payment" table. This way, all the payments made by customer (payments while placing the orders, miscellaneous payments if any and the Fine amount in case of missing/broken items), will be in one place. Make appropriate changes in the Payment table to incorporate this.

    b) Missing/Broken Items: Assuming that this is just the count of Items missing/broken, this will not help us identify which product is missing/broken. This information will be required to make changes in the product inventory once you find an item missing/broken. Please consider this scenario and make suitable changes in schema.

    PRODUCTORDER
    ----------------
    a) If there are discounts applicable on specific items (say X% discount on item 'ABC' from '2011-09-01' to '2011-09-30' OR a flat Y% discount on all items ordered on a given day etc), it will be good to store it here. This helps in calculating the total order cost and keep track of all the applicable discounts for this Order. Also a separate table need to be created to store all discounts offered by the store.

    PRODUCT
    ---------
    a) Having item photo in this table allows only one photo to be stored. To overcome this, put the image data (filestream datatype) in the image details table you mentioned.

    b) StartDate and EndDate: Please elaborate on these attributes, We already have another table that contain the prices and the applicable date ranges. Is there any other use of these two attributes in PRODUCT table?
    Cheers....

    baburajv

  13. #13
    Join Date
    Sep 2011
    Posts
    39
    ORDER
    ------------

    a) TotalCost is not required since it can be derived from ItemPrice and ItemQuantity
    its meant to be a computed column. I shouldnt include it?

    b) SpecialOrderID:- From the current schema what i understand is that an order can have only one SpecialOrder (and that too for a single product). Please correct me if I am wrong and elaborate on this attribute.
    Hey sorry about that.I've edited my post. Its a one to many relationship- so one special order can have many pruducts..
    and to further elaborate.Say your customer is a family member so you charge him/her a random small price like $80 instead of $150(which is the actual cost of the items based on the prices)...thats the purpose of special order..

    c) TotAmtPaid :- Since there is a separate table (Payment) to keep track of payments related to the Orders, we do not require this attribute in this table.

    alright...

    d) TotAmountDue is not required since this value can be derived from TotalCost and TotalAmountPaid

    its meant to be a computed column. I shouldnt include it?

    ORDERDETAILS
    --------------
    a) FEE : Assuming that the use of this attribute is to keep track of the Fines collected for any missing/broken items, I suggest using the "Payment" table. This way, all the payments made by customer (payments while placing the orders, miscellaneous payments if any and the Fine amount in case of missing/broken items), will be in one place. Make appropriate changes in the Payment table to incorporate this.

    alright...

    b) Missing/Broken Items: Assuming that this is just the count of Items missing/broken, this will not help us identify which product is missing/broken. This information will be required to make changes in the product inventory once you find an item missing/broken. Please consider this scenario and make suitable changes in schema.
    yes. I considered that. But wasnt sure how that would work if say, you lost items but dont know which particular customer lost those items...i guess i could have a "LossesUnaccountedFor" attribute or something..i'll figure something out...

    PRODUCTORDER
    ----------------
    a) If there are discounts applicable on specific items (say X% discount on item 'ABC' from '2011-09-01' to '2011-09-30' OR a flat Y% discount on all items ordered on a given day etc), it will be good to store it here. This helps in calculating the total order cost and keep track of all the applicable discounts for this Order. Also a separate table need to be created to store all discounts offered by the store.
    yes also considered that...I'll do that.

    PRODUCT
    ---------
    a) Having item photo in this table allows only one photo to be stored. To overcome this, put the image data (filestream datatype) in the image details table you mentioned.

    Hey i have a question about this...Should i put the images that are pointed to by the table in my website folder? Does it matter where i put the images?.And also is there more to it besides typing the paths of the images in the Images table?


    b) StartDate and EndDate: Please elaborate on these attributes, We already have another table that contain the prices and the applicable date ranges. Is there any other use of these two attributes in PRODUCT table?


    someone (i think wim) recommended i do this if there is a scenario where the same product can have different unit price... For example, Products manufactured on September-2011 has 'X' as unit price but the ones manufactured in August-2011 has 'Y' as unit price...im not positive if i need to include this for my model though..
    Last edited by caa5042; 09-12-11 at 03:09.

  14. #14
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    <baburajv> hope you noticed the changes to Customer-Address mentioned earlier

    a) TotalCost is not required since it can be derived from ItemPrice and ItemQuantity
    its meant to be a computed column. I shouldnt include it?

    <baburajv> Think of using a computed column only if you foresee performance issues in calculating the value using the available attributes.

    b) SpecialOrderID:- From the current schema what i understand is that an order can have only one SpecialOrder (and that too for a single product). Please correct me if I am wrong and elaborate on this attribute.

    Hey sorry about that.I've edited my post. Its a one to many relationship- so one special order can have many pruducts.. and to further elaborate.Say your customer is a family member so you charge him/her a random small price like $80 instead of $150(which is the actual cost of the items based on the prices)...thats the purpose of special order..

    <baburajv> The special case which you mentioned, giving a special rate for a privileged customer can be handled in the "DISCOUNTS" table mentioned earlier. So Avoid this SpecialOrderID and Cost if they are meant only for the privileged customer-discounts"

    c) TotAmtPaid :- Since there is a separate table (Payment) to keep track of payments related to the Orders, we do not require this attribute in this table.
    alright...

    d) TotAmountDue is not required since this value can be derived from TotalCost and TotalAmountPaid

    its meant to be a computed column. I shouldnt include it?

    <baburajv> Think of using a computed column only if you foresee performance issues in calculating the value using the available attributes.

    ORDERDETAILS
    --------------
    a) FEE : Assuming that the use of this attribute is to keep track of the Fines collected for any missing/broken items, I suggest using the "Payment" table. This way, all the payments made by customer (payments while placing the orders, miscellaneous payments if any and the Fine amount in case of missing/broken items), will be in one place. Make appropriate changes in the Payment table to incorporate this.

    alright...

    b) Missing/Broken Items: Assuming that this is just the count of Items missing/broken, this will not help us identify which product is missing/broken. This information will be required to make changes in the product inventory once you find an item missing/broken. Please consider this scenario and make suitable changes in schema.

    yes. I considered that. But wasnt sure how that would work if say, you lost items but dont know which particular customer lost those items...i guess i could have a "LossesUnaccountedFor" attribute or something..i'll figure something out...

    PRODUCTORDER
    ----------------
    a) If there are discounts applicable on specific items (say X% discount on item 'ABC' from '2011-09-01' to '2011-09-30' OR a flat Y% discount on all items ordered on a given day etc), it will be good to store it here. This helps in calculating the total order cost and keep track of all the applicable discounts for this Order. Also a separate table need to be created to store all discounts offered by the store.

    yes also considered that...I'll do that.

    PRODUCT
    ---------
    a) Having item photo in this table allows only one photo to be stored. To overcome this, put the image data (filestream datatype) in the image details table you mentioned.

    Hey i have a question about this...Should i put the images that are pointed to by the table in my website folder? Does it matter where i put the images?.And also is there more to it besides typing the paths of the images in the Images table?

    <baburajv> If you choose to use filestream datatype, SQL Server takes care of storing the images (Pls refer fileStream datatype in SQL BooksOnline).

    b) StartDate and EndDate: Please elaborate on these attributes, We already have another table that contain the prices and the applicable date ranges. Is there any other use of these two attributes in PRODUCT table?

    someone (i think wim) recommended i do this in case a new version of a specific product is put into the table and i want to discontinue the old version....or maybe i misunderstood...i dont think i need o do this
    Cheers....

    baburajv

  15. #15
    Join Date
    Sep 2011
    Posts
    39
    Think of using a computed column only if you foresee performance issues in calculating the value using the available attributes.

    but where do you store that calculated value if you dont have a column for it?

Posting Permissions

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