Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2008
    Posts
    12

    how to design tables?

    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.
    Attached Thumbnails Attached Thumbnails design.gif  

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You have to either use EAV (Entity/Attribute/Value model), which is very messy, or store the attributes as XML data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2008
    Posts
    12

    I think my problem has another soloution!

    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Store common attributes as columns in the product table. Store product-specific attributes in an XML column in the product table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2008
    Posts
    12
    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.

  6. #6
    Join Date
    Dec 2007
    Location
    Appleton, Wisconsin
    Posts
    9
    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

  7. #7
    Join Date
    Jan 2008
    Posts
    12

    Wink

    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!

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by persiangulf
    So if we want to have an integrated tables,we should find a way in which the database check the integrity not the programmer.
    How do you expect to enforce relational integrity when your relationships are undefined? That makes no sense at all.

    Quote Originally Posted by persiangulf
    blindman said that you can use an xml format but I think it is not a good way because It violate the normalization rules.
    I think you need to learn more about the rules of normalization. XML has nothing to do with it.

    Quote Originally Posted by persiangulf
    So I will be so grateful if someone can help me to achieve this problem!
    Yeah. Good luck with that.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2008
    Posts
    12
    Quote Originally Posted by blindman
    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.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Dec 2007
    Location
    Appleton, Wisconsin
    Posts
    9
    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

  12. #12
    Join Date
    Jan 2008
    Posts
    12
    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!)
    Last edited by persiangulf; 01-08-08 at 15:32.

  13. #13
    Join Date
    Jan 2008
    Posts
    12
    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

  14. #14
    Join Date
    Dec 2007
    Location
    Appleton, Wisconsin
    Posts
    9
    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

  15. #15
    Join Date
    Jan 2008
    Posts
    12

    Thumbs down

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •