• RSS
  • Twitter
  • FaceBook

Security Forums

Log in

FAQ | Search | Usergroups | Profile | Register | RSS | Posting Guidelines | Recent Posts

Help with DB Model and Design

Users browsing this topic:0 Security Fans, 0 Stealth Security Fans
Registered Security Fans: None
Post new topic   Reply to topic   Printer-friendly version    Networking/Security Forums Index -> Databases

View previous topic :: View next topic  
Author Message
es0teric
Just Arrived
Just Arrived


Joined: 12 Jul 2008
Posts: 0


Offline

PostPosted: Mon Jul 14, 2008 5:34 am    Post subject: Help with DB Model and Design Reply with quote

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!
Back to top
View user's profile Send private message
es0teric
Just Arrived
Just Arrived


Joined: 12 Jul 2008
Posts: 0


Offline

PostPosted: Tue Jul 15, 2008 7:21 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Groovicus
Trusted SF Member
Trusted SF Member


Joined: 19 May 2004
Posts: 9
Location: Centerville, South Dakota

Offline

PostPosted: Wed Jul 16, 2008 3:09 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website
es0teric
Just Arrived
Just Arrived


Joined: 12 Jul 2008
Posts: 0


Offline

PostPosted: Thu Jul 17, 2008 6:56 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Groovicus
Trusted SF Member
Trusted SF Member


Joined: 19 May 2004
Posts: 9
Location: Centerville, South Dakota

Offline

PostPosted: Thu Jul 17, 2008 2:03 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website
es0teric
Just Arrived
Just Arrived


Joined: 12 Jul 2008
Posts: 0


Offline

PostPosted: Sat Jul 19, 2008 2:41 am    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
Display posts from previous:   

Post new topic   Reply to topic   Printer-friendly version    Networking/Security Forums Index -> Databases All times are GMT + 2 Hours
Page 1 of 1


 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Community Area

Log in | Register