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.

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?

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?

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?

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?

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.

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

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

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.

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.

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.

Then revenue isn't a property of product alone and doesn't belong in the product table, wouldn't you agree?

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.

threads merged