Page 1 of 2
Database Design Advice
Posted: Tue Sep 14, 2004 8:43 am
by kendall
Hello,
I am building a Products Information Table in which i have
ProductID|Name|Description|Price|
Now i would like to have options where i may want to either put the product as a "NEW","DISCOUNT","SPECIAL" product which affects the Price.
Now my question is for advice is should i
ProductID|Name|Description|Price|Special|Discount|New|
and i put the respective price for the column
or do i
ProductID|Name|Description|Price|Type|
where Type is a SET('NEW','DISCOUNT','SPECIAL') and i just adjust the 'Price' column accordingly
what is normally done
which is the effective way without causeing clutter to the database
KEndall
Posted: Tue Sep 14, 2004 8:48 am
by CoderGoblin
2nd option is preferred over 1st. (What happens in 2 months time when you have a new type ?
Personally I would prefer option 3.. another table referencing the product ID containing the prices.
;
Posted: Tue Sep 14, 2004 8:58 am
by kendall
CoderGoblin,
so many tables i really want to avoid scattering my information like this....
Ok so i should have 3rd table. Now i have a Attributes Table which goes like
AttributeID|ProductID|Name|Price
e.g.
Code: Select all
1 |2|4 @ $7.00 | $7.00
2 |3| 8oz @ $5.00 | $5.00
3 |3| 7oz @ $3.50 | $3.50
this is used to set product attibutes such as size. m l sml oz lb or if its sold as a bundle e.g. 4 @ 12.00 (when it works out cheaper to buy the 4 pck radder than 1)
So should i put it there?
Kendall
normalization
Posted: Tue Sep 14, 2004 9:08 am
by phpScott
I let coderGoblin finish helping you as he knows what he is thinking.
But I would suggest that you do some reseach on database normalization as it usually doesn't come down to how many tables you have but how easy it is to maintian all the data that you will have as well as provided flexability into your system.
a quick google search gave a whole bunch of listing, the first few looked good from a quick glance.
http://www.google.com/search?hl=en&lr=& ... tnG=Search
I'm sure it will help alot in your programming career.
Posted: Tue Sep 14, 2004 9:25 am
by kendall
phpScott,
Thanks I have read similar articles. I have to admit while i do tend to stay within these guidelines my in-experience has me rather un-confident about some of my data designs sometimes i cant even tell what is normal from not normal as they always say that although its a guidelines its not a norm to strictly adhere to
Posted: Tue Sep 14, 2004 9:26 am
by CoderGoblin
I built a system for a shop some time ago. I used the following idea. Each product may have 1 or more packets. These packets can range from 1,a 6 pack, a box of 10, a box of 100 etc. Each packet had a different price (hence separate table). PHP calculated the best price if for example you wanted 8 you could get 1x6 pack and 2xsingle. (Sometimes however the larger pakages were more expensive and you had a maximum number of smaller pakages you could purchase so we had a min and max column for the packets).
Generally speaking a product only has one price. The "packet" brought affected the price.
If you are dealing with variations in price due to attributes they are normally a different product/have a different manufacturers ordering number, hence they should be different products on your system.
Of course presenting it in a meaningful way is always the fun part
Hope you understand what I mean.
no worries
Posted: Tue Sep 14, 2004 9:59 am
by phpScott
I was just offering some advice. I have the luxury of being able to bounce ideas and possible solutions of people at work but I do understand how difficult it can be to make some of these design decisions on your own.
Hence the reason that we're here to help.
good luck
phpScott
Posted: Tue Sep 14, 2004 10:02 am
by kendall
CoderGoblin,
i sort of understand. Beleave it or not i had each item because of its attribute considered a different product thus
1234SML | the product | the sml size of the product | 4.50
1234MD | the product | the med size of the product | 5.50
which i found to be redundant in a sort of way cause the description and product would seem repeatative given that the only difference would mean to attach the size to it
i thought wwould have been a good idea to separate the attibutes because some products aldoe have only sml and med others sold in pre-defined packs others have different oz and some that have the same oz have different prices some products dont have a attribute (she sells soaps/ bath products)
Posted: Tue Sep 14, 2004 10:13 am
by kendall
The reason why i want to include these "types" was to give the person i am developing the webiste for a bit of options so that she can have a more effective website. i find he products a bit contrasting and its why its giving me such a dilemma to come up with the best solution. i want to probably go with my option 1 because these types seem to be general attributes and i dont anticipate there being any other type (what other type cud there possibly be?)
to see what i am talking about go to
http://www.handmadebodyessentials.com before i did a simple xml data page (only works in IE not NETScape) for the products info this was just to start her off
Posted: Tue Sep 14, 2004 2:02 pm
by Draco_03
Codergoblin way's the best way in this situation, always seperate your table when necessary
me for exemple i would even put type in another table
table
typeid-------descr
and i would put typeid in my main table.. so you can add change modify invent any type, it would change automatically in main table.
If you add a new type well then all you do is change the value of type in main table.
Posted: Wed Sep 15, 2004 2:54 am
by CoderGoblin
As well a the internet user's website are you also designing an administration interface. If you are then from the admin point of view it is easy to have duplicate product names. For a shop system I would design it for the shop owner requirements and needs. They are the one paying the money. The graphical design of the online shop on the internet is then considered but, with a good database structure should be relatively straightforward, although the required joins may get complicated.
It is important that the database design is a flexable as possible. You don't want to require a rewrite halfway through or if the customer adds more requirements. (Frequent occurance as they see what is possible).
Also if it the design is flexible, you can bundle the php as a "package" and resell it, charging the same potential cost for far less work

. Most online shops have a core requirement. Code to this and then build round it.
Regards
Posted: Wed Sep 15, 2004 3:07 am
by CoderGoblin
[quote="kendall"]... i dont anticipate there being any other type (what other type cud there possibly be?).../quote]
Not trying to be harsh but I will be because I think this is important... This is very bad thinking. If you are a programmer you do not know all the specifics/complexities of running a shop. You'd also be suprised at how often the naming of things become a sticking point within a project "no it is not a discount it's an offer" or some such. What happens if there is a requirement of a general discount and a privileged users discount. What happens when someone buys something and gets a different one free ?
Posted: Wed Sep 15, 2004 9:07 am
by kendall
CoderGoblin,
This is very bad thinking. If you are a programmer you do not know all the specifics/complexities of running a shop.
Oucccchh! well that was a point very well taken. I never saw things that way.
As well a the internet user's website are you also designing an administration interface. If you are then from the admin point of view it is easy to have duplicate product names.
I don't understand this point i thought you would have better wanted to avoid duplicates
For a shop system I would design it for the shop owner requirements and needs.
Lol....the shop owner hasnt a clue as to wat she want other than she want to seel stuff. So i am trying to give her a bit more "flexibility" and options
I have been theorising some scenarios concerning the way that database has a repetation of the "price" and i am heeding the advice that i should calculate prices "on the fly"
i.e.
if a product price is $4.00 then an 8oz would be 4x8 so rather than have 2 tables with a differnet price column i just calculate it fram a base price
however her system doesnt work that way(i still have to talk to her on this) thus..
if a product is 4.00 and is 1 oz. 8oz != 32 but radder 30
I am looking into your theory of
Sometimes however the larger pakages were more expensive and you had a maximum number of smaller pakages you could purchase so we had a min and max column for the packets
Now as far as i am seeing her product attributes are regularised ie there are only 4oz and 8oz but then i thought "what if she come up with a different size" (thanks goblin [wink]) what did u mean the minimun and maximum column???
Kendall
Posted: Wed Sep 15, 2004 9:58 am
by CoderGoblin
I don't understand this point i thought you would have better wanted to avoid duplicates
You want to avoid duplicates in the database if practical. This is handled by the database normalisation process. On the shop system I wrote the product name and short description were not normalised in the database. This was partly due to legacy reasons and partly due to a design decision. If the user changed a product name we wanted only that product name to change and not have to worry about checking if the user wanted all the corresponding entries to change.. It was complicated enough with a translation db join.
For a shop system I would design it for the shop owner requirements and needs.
Lol....the shop owner hasnt a clue as to wat she want other than she want to seel stuff. So i am trying to give her a bit more "flexibility" and options
what did u mean the minimum and maximum column???
Ah now this gets a bit more complicated and it is probably not needed for your system.
Pens - You have pakets of 10, 20 and 30 pens and also sell them singly (you rip open a 30 pack).
Example 1 - People have the option of buying each packet separately. No problem
Example 2 - System determines paket sizes automatically based on quantity you want to buy 35 = 1 box 10, one box 20 and 5 single.
Example 3 - People get a reduction for buying a pack of 20 rather than 20 single items but can insist on 20 single if they want to.
Example 4 - People get penalized for buying the larger packs although you cannot buy more than 10 singly.
Ok fairly simplistic example, with min and max columns, you can create various combinations. They correspond to the possible purchase quantity.
Posted: Wed Sep 15, 2004 11:50 am
by kendall
CoderGoblin,
Hmmm....i wasnt going to think that far ahead however it would have been best to implement it. However after having talk to my client earlier i passed my ideas to her and some of her business structures were simpliefied enough to do the following
the "price" would be and actuall unit price and i would calculate any change in the price on the fly rather than have another table with a different price
i had an attribute table which i would have used to hold a products size as her product has different sze, range, packs etc.
i would creat a different table again to hold products on specicals so that in the admin area i can create a section where she can manage producs on specials/disconts etc.
so in all i have a database structure like
Code: Select all
-----Product info----
ID|Name|Desc|unit price|type|
e.g.
1|product1|product desc|1.00|oz
2|product2|ptoduct2 desc|2.00|pck
-----------------------
-----Product Range Attribute----
productID|Range|
e.g.
1|8|
1|4|
2|3|
-----------------------------
-----ProductSpecials------
ProductID|discountvalue%|
e.g.
2|50|
1|15|
-----------------------------
Basically my purpose would be that no matter whatever she does to the product that may affect the price the system calculates the expect price accurately using the unit price. the customer on the other hand affects the price based on the attributes select thus whether its 4 oz or 8 oz if it is on special/sale the overal price would be calculated accordingly. I have no control over "qty" as the shopping cart form i use is a plug in thus....is some one wanted they cud get 2 sets of 4oz buy just updating the "qty" field in the shopping cart area. she said when buying more oz. radder than qty the price woud be cheaper so i would code my script accordingly to adjust the price on the fly. i think i would use javascript as i dont have much control over the form to impliment this to use php to process form info.
I see this as my solutions here however im inside the box...
but relative to a broad general situation how close is this to what could be considered as normal way of doing it?
Kendall