Help with database schema I'm working on a project and I would need help with proper design a database schema. This is the application where users can add appliances in their home. For example, television, washing machine, dishwasher line, fridge, freezer. Each one of these appliances have different parameters that I need to store in a database. I do not think they are effective represent each category of appliances separately, but on the other hand, I do not know how to include them in a single table.

I only have five categories, so five tables it was not a problem, but if they got a hundred, so I do not come quite inefficient. Do you have someone experience something similar or know to help me in my case?

Thank you and sorry for my English.
You could go with a more general structure, such as this:


appliances
----------
  id (PK)
  name (such as "bedroom TV", "main freezer", etc...)


parameter_types
---------------
  id (PK)
  type (such as "STRING", "INT", ...) - You might only be to enforce this in the application code, rather than in the database.


appliance_parameter
-------------------
  id (PK)
  name (such as "Model name", "Max. operating temperature", "IP address", etc...)
  parameter_type_id (FK to parameter_types.id)

appliance_parameter_values
--------------------------
  id
  appliance_id (FK to appliances.id)
  appliance_parameter_id (FK to appliance_parameters.id)
  value



This could be populated as:


appliances
ID  | name
----+------------
1   | Marcel's TV
2   | Main fridge
3   | coffee maker

parameter_types
ID  | type
----+---------
1   | STRING
2   | NUMERIC

appliance_parameter
ID  | name        | parameter_type_id
----+-------------+------------------
1   | model number| 1
2   | colour      | 1
3   | IP addr     | 1
4   | weight (kg) | 2

appliance_parameter_values
ID  | appliance_id  | appliance_parameter_id | value
----+---------------+------------------------+-------------
1   | 1             | 1                      | TV-01A
2   | 1             | 2                      | Magenta
3   | 1             | 3                      | 192.0.0.1
4   | 1             | 4                      | 3.2
5   | 3             | 3                      | 192.0.0.2
6   | 3             | 4                      | 1.2
7   | 3             | 2                      | grey
8   | 2             | 1                      | FRDG-001


In this example, you don't need to create specific columns for appliances. You create date for an appliance parameter in a specific table, then populate values into appliance_parameter_values when you need to. This system can handle any number of appliances with any number of parameters, as long as you can add the appropriate records for them.