The problem is this: What is the best database schema for the orders of an application which sells various kind of products or services? In matter of fact we want something more general since the application can sell almost everything.
Take the assumption that you have all your data inside a database. There are no data stored in php or xml files. All the data are inside the database. So for that “everything” we underlined above we can be sure that it is a table and that this table has a primary key composite or not. But let’s make it even simpler and say that the primary key is simple and the corresponding column name is “id”.
Next assumption is that the primary key of the tables are always integers as is most commonly used!
So the schema with which we start is as you see in the image above. We have the “customer” (or the “user”, whatever you want to call it) table and the “order” table with attributes which generally describe an object. So “a user has many orders” and therefore no wonder we have the foreign key inside the order table.
The difficult part starts when you want to see what to charge. You know what you are charging is an entity but you might want to charge many different entities! All of them share a common attribute, the price but may be that this is all what they have in common. In fact consider you have N entities that can be charged. This is N tables. Consider N to be 10 or even 20! and you see how big this can be. Anyway for our example I created three tables “chargables”, “chargable_stuff”, “chargable_other_stuff”.
I will present three possible solutions I could think of…
First option is that you create ONE TABLE FOR EACH chargable entity you might have. As you see there are three tables with the same attributes, two columns for the ids (yes this is a MANY-MANY relationship between order table and “chargable” table) and one column for the amount of items bought. The only difference among them is which table the connect with the “order” table.
So you have to maintain N of these tables, as much as there are chargable entities.
Here you have a VERY dynamic solution since the order_item table has its own id (auto increment), and instead of referencing to chargable tables by foreign key it contains the NAME of the chargable table and its id.
Actually the table name is not a simple VARCHAR column but references to the table “chargable_table” which there you have all these table names listed more clearly. But this plays little role in our problem, yes indeed I could have left the “chargable_table_name” column inside the “order item table”.
Now you only have to maintain a couple of tables but on the other hand you can forget of applying standard join queries. You probably will need at least two sql statements for each query you might want to implement.
You have a single table called “item” where all the chargables are referring to with a foreign key! The price since it is a common attribute is contained inside the “item table”. The “item” table is connected with the order table with a MANY-MANY relation which also has the amount attribute.
So once more you have to maintain a couple of tables. In addition you have to maintain N foreign keys inside each chargable table. Moreover you have to make sure that a chargable when it references to an item (through item_id) there is no other chargable which happens to have an item_id of the same value!
Which solution do you think is best? Or even better… do you have a new solution of your own? I would love to hear about it!!
PS: I also attached the mysql workbench file for anyone that would like to play with the above ER diagrams