Simple database structure advice

Question

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.

Answer 1

The theory is you should isolate elements of diferent purpose, function or character, in this case fields like invoiceNum, make, model, location and type are normal candidates to live in their own tables, and have only a reference to their id numbers in this table, but it depends a lot on the application itself.

What you are asking for it's called 'Normalization', and you can get a lot of examples from the web:

https://blog.udemy.com/normalization-in-database-with-example/ https://en.wikipedia.org/wiki/Database_normalization

The simple advice is: yes, at least get 1st and 2nd normalization forms when possible, it will make your life easier in the future.

EDIT: I forgot to answer 'why' you should separate different purpose data in different tables: There are a lot of reasons, but even when better explained in the links provided, we can resume it with these points:

• Your data will have less redundancy, you can point to a number instead of repeat the same string through thousands or millions of records.
• Your data will be easier to modify: if you have to change 'Motorbike' by 'Water Motorbike' you will change it in one row only ( easier, and a lot faster in comparison ).
• Your data will be more consistent, as you can have the same concept in different tables, and update all the 'Motorbikes' of one table, but those of other table. If you have normalized your tables, you can change it in one table and would affect all involved tables, not only that you can remember the data was in.
• As it's more organized, insertions, updates and searches gets incredibly improved.

This is normally called ACID ( Atomicity, Consistency, Isolation, Durability ), the properties of a well designed database. I'm sure I forget some basic concepts, but there are a lot of reasons to use normalization.