Help with DB Model and Design

Networking/Security Forums -> Databases

Author: es0teric PostPosted: Mon Jul 14, 2008 5:34 am    Post subject: Help with DB Model and Design
    ----
I'm helping a friend of mine start a small, local graphics design business. It's basically your typical tshirt/apparel/stickers/hats/magnets type of thing. I have never done much work in the way of databases, but I figured it would be a great opportunity for me to learn as well. Databases are the area of computing I have always stayed away from... until now I guess Smile

I want to implement a database solution for his website, probably using MySQL (just because it's free and seems to be widely used, no other great reasoning there). I realize it's important to put a lot of thought into the database design, because once it's been implemented it becomes much more difficult to change later.

With that in mind, I need some advice on how to structure the db. I know there are multiple ways to accomplish this, but I wanted input on recommendations and possible drawbacks I might not be considering.

I figure it's a given that there should be tables for the following entities:

Customers
Orders
OrderDetails

I'm not sure what the best way would be to structure the tables for the products though. I could have a 'Products' table, with a field for category (shirts, hats, stickers, etc), or do you think each of those categories should be an independent table? For example, have a table for shirts, a table for hats, etc.

Also, as a follow-up question, I was wondering how this database could be remodeled later if it turns out a change is needed? Say you want to add a table or just a field to a database that's already in production. Is that just a matter of a few trivial commands, or is it an intricate process with risk of data loss?

Thanks for the help!

Author: es0teric PostPosted: Tue Jul 15, 2008 7:21 am    Post subject:
    ----
Oh and in case it matters, I'm not being paid for this... so don't feel like I'm trying to get a free ride by asking for some help Laughing

Author: GroovicusLocation: Centerville, South Dakota PostPosted: Wed Jul 16, 2008 3:09 am    Post subject:
    ----
It is sort of hard to explain how to do this without knowing your level of experience with a database. I'll see if I can get you started in the right direction.

Basically, an online store has products. The function of the store is to sell products to customers. The clients request products through orders. Starting with that, you have three tables; customers, orders, and products.

The customer table is pretty easy; name, shipping address, email, etc. The only real potential problem is if you choose to also store customer's credit information. If you do not take all the proper steps and provide proof that you have taken all possible measures to protect that data, you create a huge liability. I would recommend using a third-party to handle the transactions (PayPal, Google, etc). And of course, each customer must have a unique ID.

The product table is usually pretty easy, unless you have multiple types of a single product (ie, a green sweater, a red sweater, a blue sweater). If that is the case, then you need multiple product tables. Perhaps it would be better to call one 'products', and another 'inventory'. The product table would contain general; categories of products, such as sweaters, shorts, sunglasses, etc. The inventory table would contain specific types, again referring to red sweater, green sweater, etc.

The order table is a little harder to explain. An order form is made up of information from other tables. For instance, it would likely contain some information about the customer, and lines that contain specific order information, such as quantity, price per unit, product number and description. In fact, you would have a separate table called something like 'lines' (you call it OrderDetails) that would contain the specific order information, which would include a column for order id.

The order table would likely contain customer id, date of order, date of shipment, order cost, and an order id. When an order is visually put together, the information can be retrieved from the 'lines' table by order id, the customer data would be retrieved from the customer table by customer id, etc.

In its most simple form, you would really only need the following tables:
Customer
Products
Inventory
Order
Order Lines

I'm not sure if this helps much. Database design in some ways is really simple, because the data sort of tells you how to structure it. At the same time, it can be very difficult to make sure that the design does not end up overly complex.

EDIT: I forgot to include this link. It has some specific examples that may be helpful:
http://www.tekstenuitleg.net/en/articles/database_design_tutorial/1

Author: es0teric PostPosted: Thu Jul 17, 2008 6:56 am    Post subject:
    ----
Thanks for the awesome reply and helpful link. I'm definitely going to go with most of your suggestions, but the part I am most unsure about is how to structure the Products table.

This is a graphics design business, primarily clothing. Tshirts, sweatshirts, hats, girl's shirts, and then some misc items such as stickers or posters. Because these products differ so greatly, would you recommend putting each one in its own table? Table for posters, table for stickers, table for shirts, etc? If so, that would mean adding tables when he inevitably decides to add more products. Would that be a big deal, or is adding a table fairly trivial?

Thanks again.

Author: GroovicusLocation: Centerville, South Dakota PostPosted: Thu Jul 17, 2008 2:03 pm    Post subject:
    ----
By convention, one should never alter the database. A good design will allow for new types of items to be added without altering the database. Adding a table can be problematic. Not only do you add the table, but then you need to alter existing tables that refer to the product tables, and altering a table can cause problems if one is not careful. Adding a table is trivial, but making sure all of the references remain intact and correct can be very difficult. You don't think Amazon or Ebay alters their database every time they add a category of product? Smile

Author: es0teric PostPosted: Sat Jul 19, 2008 2:41 am    Post subject:
    ----
Gotcha.

Thanks for the guidance, and I'll post here with an update on the status and design concept I end up deciding on in a few days.

Thanks!



Networking/Security Forums -> Databases


output generated using printer-friendly topic mod, All times are GMT + 2 Hours

Page 1 of 1

Powered by phpBB 2.0.x © 2001 phpBB Group