Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2010
    Posts
    14

    Database Design Questions

    Assuming that I have a database named productcatalog, which consists of different tables (allfruits, allbreads, alldrinks, allmilkpowders). Inside those tables are fields like Quantity, Price, Sales, etc. On different machines or PCs, they would update some of the fields in those tables. The problem in this simplistic database design is that I would not know which machine updates those fields. For example, I would need to retrieve records say how much Quantity of Apples remained in allfruits table from a particular machine. I suppose that I need to create another table, say allmachines, which would contain all those MachineIDs. But I am not sure how do I link up the allmachines table with those tables in the productcatalog, such that I am able to retrieve records of (allfruits, allbreads, alldrinks, allmilkpowders), from a particular machine, identified by the MachineID.

    I would appreciate some detailed explanation, as I am quite new to Database Design concepts. thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    first off having different tables for different product groups is not a good design.

    the whole idea of a database server is that you have one central datastore to contain the data, accessable by any network device that has a need to access that inforamtion. there is on repository of information. so you dont' have your issue of where to go to find whatever, although if you must persist with your current design idea that is easily enough achieved if you named each computer a suitable name eg the PC with the fruit data could be called 'allfruits' and so on.

    I woudl suggest you have a single table called products, and if there is sufficient data for each type of product have a sub stable for each product group that requires it.

    as this isn't a MySQL specific design issue, do you want this moving to the Database Concepts & Design forum where you may get a better answer on what is a theoretical design?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Is this the whole question that your teacher gave you or just a part of it?
    Have you done any work towards it at all or would you like us to do it all for you?

  4. #4
    Join Date
    Mar 2010
    Posts
    14
    This is NOT a question which any teacher give me. It is part of work-related issue. I do not mind move to Database Concepts & Design forum, where I can get a better answer. I would post my questions more clearly over there.

    My idea of having different tables for different product groups is to have more organized tables, as those different types of foods may grow to huge number over times. And also easier retrieval of data information.

    I think probably I did not pose my questions clearly. Each PC or machine can update any of the tables, and I want to retrieve the data information as per PC or machine.

    I do not get your suggestion of sub-table for each product group. Could you elaborate?

  5. #5
    Join Date
    Mar 2010
    Posts
    14

    Database Design Question: Many-to-Many relationship?

    say I have a database named productcatalog, which consists of following tables (breads, fruits, drinks, milks, etc). Those tables contains fields like (Description, Price, Quantity_Sold, Revenue, and their Primary Key).

    Each PC or machine will update to those tables.

    I would like to retrieve records from those tables, as per machine basis. Meaning that say with MachineID=101, I want to retrieve records of Quantity of Breads sold, and with MachineID=102, I want to retrieve records of total Revenue from the sale of Fruits, and with MachineID=103, I want to retrieve records of the Price of all Drinks.

    Should I create another table named machines, with MachineID as the Primary Key, and other fields like (machine_location, machine_IPAddress, etc)?
    I think that the machines table's relationship with those tables (breads, fruits, drinks, milks) to be many-to-many relationship. Correct me if I am wrong. One particular machine can update any of those tables, and any of those tables can be updated by any of the machines with different MachineID.

    In that is the case, if my logical thinking is correct, does that mean that I have to create another association table?

    say for machine table (PK=MachineID) and breads table (PK=BreadsID), a association table named machine_breads (PK=MachineID,BreadsID)

    say for machine table (PK=MachineID) and drinks table (PK=DrinksID), a association table named machine_drinks (PK=MachineID,DrinksID).

    If my above Database Design is correct, how do I do query, say I want to retrieve records of total Revenue from MachineID=103?

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I suggest that a more effective design would be to put the attributes common to all products into a single Products table. Machines would then be associated with products and querying the total sold and revenue only needs to reference one table.

    Splitting attributes that depend on a common key into multiple tables is a potential flaw because it leads to ambiguity and means you may need to duplicate constraints and other logic.There is a design rule called the Principle of Orthogonal Design that seeks to avoid such designs. Also DRY.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    so you have a table for product types and products
    the pridcut type allows you to easily identify the product type in the products table
    if you want to make life more complicated your product type could also have a self referencing fk in product types so you can identify the parent product type

    ferisntance its quite common to have, say 'fruit & vegetables', then a fruit category, then say citrus, then say oranges, lemons, limes and so on as end categories.

    a customer may arrive at your application and want to look at fruit & veg, Veg and so on. it allows you as developer to show similar products eg if they request oranges you could display all oranges and fruit in the same child category at the same level as oranges.

    I doubt very much you need worry about the amount of items any specific database can store. even the humble Access using JET can store 2Gb of data for a server backend you are probably on limited by your imagination
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2010
    Posts
    14
    I understand your suggestion of putting all those tables (breads, fruits, drinks, milks, etc) into a single table called products. Do I need to create another table called machines, with MachineID as the Primary Key? How do I then associate the machines table and products table?

    For example, I would need to query what is the total revenue from all breads from MachineID=101. How could I do that, if it is a single table called products?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    no
    thats sales
    a sale is registered from a specific computer/machine
    a sale has nothing (directly) to do with a product, expcet that a sale may comprise many products, that many may be a quantity of apples, or a mix of different types of fruit.

    I think you need to do some basic reading on table design and normalisation
    Fundamentals of Relational Database Design -- r937.com
    and
    The Relational Data Model, Normalisation and effective Database Design
    seem to be oft referred texts
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2010
    Posts
    14
    thanks for the links, I have been reading those kind of Database Design material, but when it comes to actual implementation, it is still as difficult.

    yes, a good idea is to the Sales/Revenue field put into another table Sale. Then I would need to associate the Sale table with the Machine table, and it seems to me it should be a Many-To-Many relationship.

    I would need to get the overall Design Database architecture right, else things would get more messy, as the database grows to include more complex stuffs. For the present, I would need to extract information, as per machine basis.

  11. #11
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by tanthiamhuat View Post
    I understand your suggestion of putting all those tables (breads, fruits, drinks, milks, etc) into a single table called products. Do I need to create another table called machines, with MachineID as the Primary Key? How do I then associate the machines table and products table?

    For example, I would need to query what is the total revenue from all breads from MachineID=101. How could I do that, if it is a single table called products?
    I'm not clear if revenue is supposed to be a property of a product alone or of a particular combination of product and machine. If a product belongs to more than one machine then would you want just one revenue figure for that product or one figure for each machine?

    I'm not going to do your homework for you but I assume you must have some table that associates machines to products and that that table may or may not be the one with the revenue figure in it - depending on how you answer the above question.

  12. #12
    Join Date
    Mar 2010
    Posts
    14
    revenue is a property of a product alone.
    If a product belongs to more than one machine then would you want just one revenue figure for that product or one figure for each machine? Very good question. I would need one figure for each machine for each particular product.

  13. #13
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by tanthiamhuat View Post
    I would need one figure for each machine for each particular product.
    Then revenue isn't a property of product alone and doesn't belong in the product table, wouldn't you agree?

  14. #14
    Join Date
    Mar 2010
    Posts
    14
    yes, I would need to pull out the revenue out of the product table. revenue = price * quantity_sold. Does that mean I need to create another table for the quantity_sold?

    I am getting more confused, as more tables are being created, and I still do not know how do I associate the machine table to all those tables created.

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    threads merged
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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