| Author |
Topic |
|
Ciwan
Starting Member
5 Posts |
 Posted - 2011-01-25 : 07:20:23
|
Hello Friends.I realize that any good data driven web application needs a nicely designed database at its backend. A good friend of mine owns a print shop and he asked me to develop him a simple website where people can order prints online. He says he doesn't want to offer too much flexiblity to the end user, cause he knows that makes things complex.He wants it like so:- User visit website
- Clicks on Product category
- A table is shown with prices for x prints and an order button at the end of each table column (where x = the number of prints. For example 500 prints for £49) (note user has no option to add custom number of prints)
- When Order button is clicked for a certain column > User is taken to Login or Registration if not already logged in.
- Once logged in, user is taken to the next stage of the ordering > that's where he/she gets the option to upload own artwork for print.
- Order is then added to basket. And all the steps above can be repeated for a different product (if needs to be done).
- When placed as many orders as needed, clicks on Checkout.
- User is taken to payment and if payment successful, the order(s) is placed in the database.
The hard thing about this project is that each product is different ! i.e. each product has attributes/properties that others don't !!!! For exampleNCR Forms, have Perforation Options, Number of Sheets per NCR Set, Squencial Numbering to start from, quantity, price and artwork ... etcWhile a business card does not have all that stuff, all a business card has is the artwork, a boolean for whether to print on one side or both, quantity and price. (size is always same for a business card in his shop).This was just two examples, in total there are 7 to 9 product categories. I have attempted to design the database, and the best I've come up with is the following: You guys can probably see many problems with this, and that's why I am here. I need my database to be perfect before moving on.I would GREATLY appreciate any help with this.Thank You Guys.Here to Learn.![]() ![]() |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-01-26 : 15:11:05
|
| Well that's nice to include a diagram. A lot of time posters say all this stuff and we can only imagine how their Database is set up.You have NCR_ID in the Order table, not linked to anything. Then you have a Product_ID pointing to the NCR Table. What's going on there?The NCR_Product Table - is that Product attributes (ie. fixed) or Order attributes for the NCR? i.e. Are there different types of NCRs to choose from, and the customer chooses some options after that? For example, the price is from the Catalog or that is the Price in the Order? Remember, your Friend may wish to change the Catalog Price but that should not affect what the customer is paying! |
 |
|
|
Ciwan
Starting Member
5 Posts |
Posted - 2011-01-27 : 03:51:08
|
quote:
Originally posted by denis_the_thief
Well that's nice to include a diagram. A lot of time posters say all this stuff and we can only imagine how their Database is set up.You have NCR_ID in the Order table, not linked to anything. Then you have a Product_ID pointing to the NCR Table. What's going on there?
Hello Denis.Yep I always try to make things clear by adding a screenshot of what I'm trying to get across. We homo-sapiens are visual beings :)Non-Carbon Required (NCR) paper is a product. Essentially it is always the same, but it has a few tweak options that the customer can choose from (e.g. Squencial Numbering to start from 103490)Here is a screenshot of the shop front end which I have coded. What you see there are the product categories. Then a user clicks one and gets an order table. The one you see below is what the user gets when he/she clicks on Business Cards: If they click the Order link on any column, they are then taken to the tweaking page. The tweaking page would look different for each product category (but I don't know how to design my database to allow for that) :(The way I have my database in the ERD is that I've given each product (more or less each product) its own table. < but I know that is wrong some how.I wish to have all products in one table, but each product to have a category. And depending on that category, each product is suppose to get certain attributes / tweak options.The issue is I'm a newbie to this, and I don't know how to implement that in my database.That is why I'm here asking for help from the Pros :(Thank You.Here to Learn. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-01-27 : 08:44:03
|
| I don't mean to give you a hard time. But the price issue is a big one! For example you have in the NCR_Product table Price. Is that the List Price or the Sale Price? Don't you need both??? Or are you OK with one?Maybe after that is worked out, other things could fall into place a bit clearer... |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-01-31 : 09:07:00
|
quote:
The way I have my database in the ERD is that I've given each product (more or less each product) its own table. < but I know that is wrong some how.
I wouldn't say that is neccessarily wrong. It is better to put like things in the same table but that is open to interpretation.You could make a Product table and combine several of your tables. I would definitely move Qty into the Order_Item table. You could have a ListPrice in your Product table and an OrderItemPrice in your Order_Item table. |
|
|
|
Ciwan
Starting Member
5 Posts |
Posted - 2011-01-31 : 13:23:58
|
OK I did a whole re-design of the database. Now each product will have a category, and each category would have certain attributes.What do you think ? Problem solved ?I do realize that there will be a lot of Type Casting needed.Here to Learn. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-01-31 : 15:13:37
|
quote:
Now each product will have a category, and each category would have certain attributes.What do you think ? Problem solved ?I do realize that there will be a lot of Type Casting needed.Here to Learn.
I think it's way better than the original. I think you need some kind of a Price Matrix like: ProductID, Qty, UnitPrice since, at least for your Business Cards, your List Price per unit could vary by Qty.That Category/Attribute approach could add a lot of extra work. So I guess I haven't helped you with your original question of what to do when you have many things that are similar yet different. There might not be a correct answer. I was mainly concerned how you orignally had the quantity (in several Product tables rather than the OrderItem table) and the price (needed in both Product i.e. list price and OrderItem table ie Ordered Price) set up. Maybe now that you have a Product table, you might try to fit all types in there, hard to say... |
|
|
|
Ciwan
Starting Member
5 Posts |
Posted - 2011-02-01 : 06:35:19
|
quote:
I think you need some kind of a Price Matrix like: ProductID, Qty, UnitPrice since, at least for your Business Cards, your List Price per unit could vary by Qty.That Category/Attribute approach could add a lot of extra work.
At the print shop the cost is calculated per printed sheet. So if a user orders 505, the shop will charge for 520, and send him/her 520.As for the hard work, I do agree it'll be hard work, but I think there is no way of avoiding that.What do you think ? :)Here to Learn. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-01 : 07:05:09
|
| The last db design you posted is what is called an EAV-model (entity-attribute-value) and is a well-known way of solving these kinds of problems. Unfortunately the nature of this model has some serious implications, among which there are not constraints between the data, and you are more or less restricted to the varchar data type. You will also face serious performance problems if you database grows large and you need to do reporting and there are various other problems as well. To shed some light on more of them please read this: http://en.wikipedia.org/wiki/Entity-attribute-value_modelThere are some alternatives, but none are really optimal in a relational database. Some alternatives could be sparse and/or xml columns.- LumbagoMy blog-> www.thefirstsql.com |
|
|
|
Ciwan
Starting Member
5 Posts |
Posted - 2011-02-01 : 07:18:50
|
| Nice ! So what's your basically saying is my current design works best !Here to Learn. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-01 : 08:02:10
|
| What I'm saying is...that in situations like this you need to find the design that is the least bad :) Since your'e doing this for your friends print shop (probably not in the GB's of data) an eav design can actually work pretty well. And if you're not overly concerned with performance and you can deal with everything having a single data type, it would be my design of choice.- LumbagoMy blog-> www.thefirstsql.com |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-02-01 : 10:58:44
|
quote:
Originally posted by Ciwan
quote:
I think you need some kind of a Price Matrix like: ProductID, Qty, UnitPrice since, at least for your Business Cards, your List Price per unit could vary by Qty.
At the print shop the cost is calculated per printed sheet. So if a user orders 505, the shop will charge for 520, and send him/her 520.
I just was thinking how you have in the Business Cards. Something like:ProductID Qty ListPricexxxxx 500 49xxxxx 1000 66yyyyy 500 15 ... |
|
|
|
|
Database Architecture For Online Print Shop