How to represent following information in postgres table?

The information I need to store has the following format

category, command, options, description

options depend on commands and can have many different values. e.g

'SetBack', 'set_fan',  [ (0,ON), (1, OFF), (2, AUTO) ], 'Sets the fan value'
'SetTime', 'set_time', [0-99 Minutes], 'Sets the time value'
'SetHour', 'set_hour', [0-12 Hours], 'Sets the hour value'
'SetFanOptions', 'set_fan_opt', [ (0,'Constant','constant value'), (1, 'Air Mixture', 'Sets Fan Air Mixture'), (2, OFF, 'sets off fan') ... ], 'Sets the Fan Ait Mixture value'

'options' field has multiple types of values.

Wha开发者_如何学Ct would be the best way to represent this information in postgres? Also, Should I use one table or multiple tables?

---

The category, command, and description are pretty straight forward varchar columns for some sort of "command" table:

create table commands (
    command     varchar not null primary key,
    category    varchar not null,
    description varchar not null  -- Or text if the description will be large.
)

And the options should have their own table:

create table command_options (
    command varchar not null references commands(command),
    idx     int     not null check (idx >= 0),
    value   varchar not null, -- Not sure if these two column
    label   varchar     null  -- names make sense in your context
)

So, your set_fan options would look like this in command_options:

INSERT INTO command_options
(command, idx, value, label)
VALUES
('set_fan', 0, 'ON',   null),
('set_fan', 1, 'OFF',  null),
('set_fan', 2, 'AUTO', null);

And set_time sort of like this:

('set_time', 0, '0-99 Minutes', null)

And set_fan_opt:

('set_fan_opt', 0, 'Constant',    'constant value'),
('set_fan_opt', 1, 'Air Mixture', 'Sets Fan Air Mixture'),
('set_fan_opt', 2, 'OFF',         'Sets off fan');

I'm not sure what the difference between "category" and "command" is or how much the database needs to know about the options but hopefully the above will get you started.

---

When I am designing a database schema, I map out the different tables that I think I will need then look at their relation. For example, you want to look at if Table A will have a 1:1 or 1:many mapping with table B. Just to get started. Try to visually map it out. This looks like a fairly simple db so it wont take long.

Then map out the columns that you plan on using. Make sure you can uniquely identify a record.

---

I am not sure I understand the question because I don't recognize your "code" as anything remotely to do with SQL, but anyhow.

For SetBack, SetTime and SetHour I would use an integer column with the approriate check constraints to make sure only valid numbers can be stored.

If SetFanOptions is a single value out of the ones you listed, I'd use an integer column as well (again with approriate check constraints)

If you can have multiple fanOptions per fan(?) you will need a one-to-many relation to another table.