15 tables property redundant with mysql

Question

I was faced with case that should have 15 tables of data having same property for all 15 tables but with different purpose for each table, and just have same property name because I need to show each table with different record.

All my 15 tables contain this property [no,date,commentary,price,stock]

I already creating 15 table with all same property with MySQL, but I think it's wrong about my way in creating this database, because it's look redundant for the property.

I don't know much about designing a database.

Read a bit about [database normalization](https://en.wikipedia.org/wiki/Database_normalization), and in particular the first 3 normal forms, that should give you a rough idea of how to model tables. — Augusto, Aug 10 '15 at 19:54
Could you have one table with a new column for "Purpose"? Can the same thing be in more than one of your 15 tables? — Patrick87, Aug 10 '15 at 20:08
You correctly managed to figure out that this isn't the way to go, so have my upvote - I hope this might bring attention to your question so you can get quality answers. Not many people figure that step out, you should be proud. — N.B., Aug 10 '15 at 22:04

score 3 · Accepted Answer · edited May 23 '17 at 11:58

Do you have just different types of items in your database? Read about normalization in MySQL (Stack Overflow answer).

Instead of creating 15 tables with same columns, you can e.g. add one column which tells that what type of item is on that row. So your columns could be something like no, type, date, commentary, price, stock

For example, if you had 2 separated tables before...

Table 1

-----------------------------------------------------------
| no   | date        | commentary    | price     | stock  |
-----------------------------------------------------------
| 1    | 2015-08-01  | Lorem ipsum   | 10.00     | 6      |
-----------------------------------------------------------
| 2    | 2015-08-07  | Dolor sit     | 25.00     | 3      |
-----------------------------------------------------------

Table 2

-----------------------------------------------------------
| no   | date        | commentary    | price     | stock  |
-----------------------------------------------------------
| 1    | 2015-08-03  | An usu nemore | 15.00     | 10     |
-----------------------------------------------------------
| 2    | 2015-07-30  | Eam at eros   | 30.00     | 1      |
-----------------------------------------------------------

You can create just one table which contains the data from tables 1 and 2. But this time table contains new column type which tells what type of item is on the row. In this example the data which was in the Table 1 before has type 10 and data from Table 2 has type 20.

------------------------------------------------------------------
| no   | type | date        | commentary    | price     | stock  |
------------------------------------------------------------------
| 1    | 10   | 2015-08-01  | Lorem ipsum   | 10.00     | 6      |
------------------------------------------------------------------
| 2    | 10   | 2015-08-07  | Dolor sit     | 25.00     | 3      |
------------------------------------------------------------------
| 3    | 20   | 2015-08-03  | An usu nemore | 15.00     | 10     |
------------------------------------------------------------------
| 4    | 20   | 2015-07-30  | Eam at eros   | 30.00     | 1      |
------------------------------------------------------------------

Now you can execute MySQL query

SELECT * FROM tablename WHERE type = '10'

to get similar results than before when selecting all from Table 1.

15 tables property redundant with mysql

1 Answers1