Hi all,
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.

You have to either use EAV (Entity/Attribute/Value model), which is very messy, or store the attributes as XML data.

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.

Store common attributes as columns in the product table. Store product-specific attributes in an XML column in the product table.

Thank you,
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.
Thanks

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!

How do you expect to enforce relational integrity when your relationships are undefined? That makes no sense at all.

I think you need to learn more about the rules of normalization. XML has nothing to do with it.

Yeah. Good luck with that.

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.

O.K so now can 1 account be used by more then one user or is there one account for every user.
can one user have multiple accounts or again does every user have one and only one account

the "buys" table is just a table for what the user is buying.

What does the pruduct type table refer to

does every product have a different picture or can one picture work for many products.

Could the warranty name tell you what the duration would be Example:

Manufacture warranty 3 years
Distributer warranty 1 year

In other words does a perticular warranty always have a certain duration.


That should be about everything I need. Thanks

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.
Thanks

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.

Here Goes:

ProductNames
PNAME_ID P/K
PNAME_NAME

Can be any product, Internet Card, Phone Card, ETC...
--------------------------------------------------------
Manufacturers
MANU_ID P/K
MANU_NAME

Can be any Manufacturer(Factory) even ISP's Name!
---------------------------------------------------------

Warranties
WARR_ID P/K
WARR_NAME
WARR_DURAT

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.
------------------------------------------------------------
Educations
EDU_ID P/K
EDU_DESC

-----------------------------------------------------------
Users
USER_ID P/K
EDU_ID F/K
USER_FNAME
USER_LNAME
USER_UNAME
USER_PASSWORD
USER_GENDER
USER_STARTDATE
USER_ENDDATE
USER_EMAIL
USER_PHONE
USER_ADDRESS
USER_ACCTBAL

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.
-------------------------------------------------------

Purchases
PURC_ID P/K
PROD_ID F/K
USER_ID F/K
PURC_QTY
PURC_TOT
PURC_DATE

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.

-------------------------------------------------------
Products
PROD_ID P/K
PNAME_ID F/K
MANU_ID F/K
WARR_ID F/K
PROD_MODEL
PROD_PPRICE
PROD_SPRICE
PROD_QTYAVAL
PROD_DATE
PROD_DESC

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.

-----------------------------------------------------

Picture
PICT_ID P/K
PROD_ID F/K
PICT_LOCAT

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.

-------------------------------------------------------------------------

Well that is my humble thoughts, I hope it doesn't get shot down by everyone else to bad but I think it works. I would like to here thoughts on this just for myself as a way to learn more. Thanks

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.