如何在 postgres 表中表示以下信息？

发布于 2024-11-06 18:48:46 字数 598 浏览 0 评论 0原文

我需要存储的信息具有以下格式

category, command, options, description

选项，具体取决于命令，并且可以有许多不同的值。例如

'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'

“选项”字段有多种类型的值。

在 postgres 中表示这些信息的最佳方式是什么？另外，我应该使用一张表还是多个表？

原文

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.

What would be the best way to represent this information in postgres? Also, Should I use one table or multiple tables?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

探春 2024-11-13 18:48:46

类别、命令和描述对于某种“命令”表来说是非常简单的 varchar 列：

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.
)

并且选项应该有自己的表：

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
)

因此，您的 set_fan 选项在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);

set_time有点像这样：

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

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');

我不确定“类别”之间有什么区别和“命令”是数据库需要了解多少选项，但希望以上内容可以帮助您入门。

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.

回复收藏 0 原文