Newbie to dba. I'm creating a php/mysql inventory list and would like some advice before I start, on structuring my table(s) and database.
The inventory database will eventually contain hundreds of items, ever increasing (potentially to thousands). The current data is contained in a .xls sheet which I have access to, it contains the following;
items
(dummy data for display purposes)
+--------+-------+------+-----------+---------+------+-----------+----------+----------------+----------+--------+----------+
| itemId | invNo | make | model | ip | room | dataAdded | dateMod | description | location | type | status |
+--------+-------+------+-----------+---------+------+-----------+----------+----------------+----------+--------+----------+
| 1 | 234 | HP | 765 | 123.123 | 54 | 01-02-16 | 02-01-16 | some desc here | floor 1 | server | active |
+--------+-------+------+-----------+---------+------+-----------+----------+----------------+----------+--------+----------+
| 2 | 765 | Dell | Precision | 123.123 | 34 | 02-01-16 | 03-01-16 | some desc here | floor 3 | pc | active |
+--------+-------+------+-----------+---------+------+-----------+----------+----------------+----------+--------+----------+
| 3 | 987 | Dell | Precision | 123.123 | 65 | 05-09-15 | 07-09-15 | some desc here | floor 1 | server | inactive |
+--------+-------+------+-----------+---------+------+-----------+----------+----------------+----------+--------+----------+
Is it ok for me to add all these columns into the one table in my dtabase, or should I seperate the columns across different tables?
For example, should I have a table for location
, type
and status
- if so, why?
Is there any disadvantage to having all the columns in the one table?
The final web application will allow users to create, read, update and delete items. As well as search for items and filter by type/location etc...
I normally use MySql Workbench or PhpMyAdmin to create and design the database.
Any advice is appreciated.