in sql connecting transaction table with different kinds of transactions


I am making a mysql database for a restaurant.

I have a table called: *tbl_contents* which stores all the contents used in the preparation of different menu items.
Now I have to maintain a table for all the expenditures. These expenditures can be "purchasing contents" or some regular expenditure like electricity bill or rent of the restaurant.

How do I store two kinds of expenditures in the same table?

I have the table tbl_fixed_expanditures and tbl_contents. If i buy something for the kitchen it is supposed to be stored in tbl_contents and if I have paid the electricity bill, it is saved in tbl_fixed_expenditures.

enter image description here


Answers:


You are essentially trying to represent inheritance in a relational database.

You have two "classes" which are similar in some ways, and different in others. My suggestion is to create a table to act as a parent to both tbl_expanditures and tbl_fixed_expanditures.

Here's what I would do:

+------------------+
| tbl_expenditures |
+------------------+
| id               |
+------------------+

+------------------------+
| tbl_fixed_expenditures |
+------------------------+
| id                     |
| expenditureId          |
| ...                    |
+------------------------+

+---------------------------+
| tbl_variable_expenditures |
+---------------------------+
| id                        |
| expenditureId             |
| ...                       |
+---------------------------+

...where tbl_fixed_expenditures.expenditureId and tbl_variable_expenditures.expenditureId both have a reference to tbl_expenditures.id.

This way, when you need to refer to them simply as "expenditures" (for example, in your transaction table), you can reference tbl_expenditures, and when you need information that is unique to either fixed or variable expenditures, you can refer to the "child" tables.

This is a very common problem with relational databases, and there are several ways of handling it, each of which have their pros and cons. IBM has a really good article outlining these options, and I highly recommend it for further reading:

http://www.ibm.com/developerworks/library/ws-mapping-to-rdb/