I want to design a database in which there is a lot of products from differenet types and each type has its specific attributes. In this database I want that all types of products that I sell, be stored in one table.(of course there is some attributes and tables between this relationship that I can understand which kind of products are sold)
The problem of this Model is that if one day I want to add a new product,I'll have to add new table -that I think is not a nice design.
Would you offer a way in which I can have all attributes of each procuct as a specific data and can store all the selled products in one table.
Excuse me blindman but I think I could not mean well. I think my problem is another thing. As I said, there are lots of products that they have their specific attributes and I want to store all specific attributes as an atomic data not as a description attribute.
If I want to have an integrated selling information, I should have all selling information together that is not possible.(all the selling from different products in one table)
for example HDD has some specific information that just other HDDs have them not other products so It seems (as the first soloution) that It should be a table,but a verity of these products exists and we can not make each product a table.It means if one day we sell a new product we should have a new table!
There is another soloution that we gather all the common attributes in one table (such as ProductID,WarrantyID,FactoryID,WarrantyDuration,Bu y,Sell, Number,...). although in this soloution is again problem because if for example I want to store some specific information of Wireless Ethernet(Or other products) ,I have to store them in description attribute that is not very nice and it is very hard to extract some statistics from database.
Last edited by persiangulf; 01-07-08 at 15:09.
Reason: typing mistake
I think at first I could not understand what you did mean. But In this model we can not have an integrated model. It is a programmer approach not DBMS and if we do not have any application, direct access can store some incorrect information in database as well as if some products have a completely different attributes for example internet cand have these attributes ID,ISP_ID,Hours,Buy,Sell,Discription. Of course this model is based on hourly cards but we have internet cards that based on duration - i.g. one month.There is phone card too that again has its specific attributes and common attributes for products are these ProductNameID,FactoryNameID,WarrantyNameID,Warrant yDuration,Model,Buy,Sell,Number,Date,Description. So if we want to have an integrated tables,we should find a way in which the database check the integrity not the programmer.
Can you explain what "buy, sell, number, and amount mean in the tables. I just can't understand that. I think I can make it work the way you need it to but I need more info on those four attributes.
amount : I made a virtual very simple bank accound that constitute of of ID of user and amount of money that he or she has in his or her account.
number : the number of products that is available.
buy : the money that a product costs for you.
sell : the money that a client should pay
Of course I should explain more :
I have to tables: Products and InternetCards and I want to store the purchase information of both in one table (Buys). The reason for this is that InternetCard is kind of products but since the its attributes is completely different , I can store it in Products table.
But the main question is that If there are a lot of products like InternetCard, what can I do? Is it possible to make a table for each product?! blindman said that you can use an xml format but I think it is not a good way because It violate the normalization rules.
So I will be so grateful if someone can help me to achieve this problem!
I think you need to learn more about the rules of normalization. XML has nothing to do with it.
blindman I am a beginner in designing database and it is not unusual that sometimes I am wrong. But as I read in the first normal form say that each column in the row must be atomic in other words ,the column can contrain only one value for any given row.
Well, you need to think of an XML column like a distinct schema. As such, normalization can be continued within the XML data.
XML is not the ideal way to store data, as it is more difficult to enforce relational integrity and normalization within XML data simply because XML is not as rigorous as RDBMS. But that same rigor is what makes it difficult to model ill-defined business rules such as yours in an RDBMS.
So, store whatever can be clearly defined using traditional tables. Store what cannot be clearly defined within XML data.
If it's not practically useful, then it's practically useless.
One user can have just one account and each account is just for one table(As I said It is a so simple internal bank account)
yes,buys table is just for this as well as each time a user buys a product it will subtract it from products table.
the warranty is sum of manufacturer and distributer(or better say union of them)
about ProductsType there is a point. I use this table because at first I wanted to choose the approach in which some specific products that can't maintain in Products table , will have their specific tables. The ProductType table give an ID to each product in Buys table that can help to determine the perchased item is from which table.(Of course as you think this approach is so bad,but I did have to!)
Tuke and blindman I am very grateful because of your help.
I attached the database code in MySQL that I hope this can show every thing.Shop.txt
If it is not complete please tell me that I answer these questions as well as every thing that you thing is not clear.
O.K. So here is what I came up with. I think that it will work for what you want. I'm hope someone will point out any flaws so that I may learn what I'm doing wrong.
Can be any product, Internet Card, Phone Card, ETC...
Can be any Manufacturer(Factory) even ISP's Name!
WARR_DURAT = Warranties Durration
You can list all warranties available. Examples:
Manufacturer Warranty Duration 3 Year
Manufacturer Warranty Duration 2 Year
Manufacturer and Dealer Warranty 4 Year
You could also add a Description column then use a abrev. Name and list the whole description in the description column. This table can have quite abit of tweaking done for different needs.
FNAME = First Name
LNAME = Last Name
UNAME = User Name
STARTDATE = Membership Start Date
ENDDATE = Membership End Date (If needed)
ACCTBAL = Account Balance
Because a USER has only one account and it is theirs alone, you can put it in the USER table.
You might want to split the address attribute into its individual parts. For me it would be ADDRESS, CITY, STATE, ZIPCODE. This let you run sales on say Zip Code or state or city. More options.
PURC_QTY = Quantity Purchased
PURC_TOT = Total for that purchase
The total was stored so that you can keep a history of the price someone paid for the product last year taking the total divided by quantity. If you need to track historical data????????? I think that would work someone correct me if I'm wrong.
PROD_PPRICE = Price you pay for the product
PROD_SPRICE = Price customer pays
PROD_QTYAVAL = Quantity on Hand
Here is my humble thoughts
Product id would be 10, Product name would be Internet card, Manufacturer would be the ISP name, Warranty if any and that field can stay NULL or default with "NONE", Model would be hourly, Etc.......
The next Product id would be 11 have the same name, manufacturer, warranty, but the model would be monthy. See where I'm going with this. I would think any product can be added to this design.
If every product ID has its own picture then put the picture in the Products table and loss the picture table. If one picture can go with many Products then put PICT_ID in the Products table as a Foriegn Key. If one product can go with many picture then keep it the way it is.
Thank you Tuke.
Your design is so better tham mine with two point :
First : If we want to store for example the hours of hourly Internet Cards or the Duration of some cards we don't have any field to put them there. Of course it is possible to put them in description that sometimes for some Internet cards is not well.
Second : I think if you put PURC_TOT from Purchases in the Users table is better because we don't have to repeat it for every purchase and it stores one time for each user.
Again Thank you for your help. If you think that I am wrong with these points please tell me.