创建 SQL 架构 (postgresql)
我在为 PostgreSQL 项目创建架构时遇到问题。
对于一个社交网站来说，如果有一个个人资料，每个个人资料分为三种：通用、教育和就业资料，因此每个个人资料需要不同的属性……我们如何在一张表中完成这一切？
create type ProfileTypeValue as enum
('generic', 'education', 'employment');
create Profiles (
id integer
type ProfileTypeValue
....?
primary key (id)
);
因为例如，如果它是教育简介，那么我们需要有机构名称等，或者如果它是就业简介，那么我们需要有雇主名称属性等。
最好只有 3 个不同的表，每个表 1 个profile 类型，不知道这是否可能......但我觉得我需要一个 if 语句来说明它是否是配置文件，包括这些属性，或者如果它是配置文件，包括这些属性等。
I'm having problems creating a schema for a PostgreSQL project.
It's for a social networking site, if there is a profile, and each profile comes in three varieties: generic, education, and employment profiles, therefore each profile requires different attributes… how do we do this all in the one table?
create type ProfileTypeValue as enum
('generic', 'education', 'employment');
create Profiles (
id integer
type ProfileTypeValue
....?
primary key (id)
);
because for instance if it's an education profile, then we need to have institution name etc, or if it's an employment profile, then we need to have an employer name attribute, etc.
Is it best to just have 3 different tables, 1 for each profile Type, dont know if thats possible… but I feel like I need to have an if statement saying if it's profile, include these attributes, or if its a profile, include these attributes, etc.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这里有几个选项
全部位于同一个表中
在此选项中，无论配置文件是什么类型，所有字段始终存在。第一次这样做太容易了，因为您只需列出所有列。然而，这确实是一个糟糕的设计，从长远来看会让你的生活变得更加困难，因为可维护性和可扩展性很差。您应该阅读数据库范式等。不要这样做。
在自己的表上主控配置文件表和配置文件类型相关详细信息
在此选项中，您将为所有配置文件创建一个表。这将包括所有公共属性。该表将确保标识符都位于同一命名空间中，并且每个配置文件都有一个唯一的 ID。对于每种配置文件类型，您将创建一个新表，其中包含对主配置文件表的外键引用。然后，您可以使用就业资料表和主资料表上的内部联接来选择所有就业资料。此设计允许您为每种配置文件类型创建约束。此外，这种设计可以让您拥有就业和教育档案。你也许应该这样做。
继承
Postges 提供了表继承的工具。您可以通过为所有配置文件类型创建基表，然后为每个配置文件类型创建子表来使用它。然后，每个配置文件类型都会继承父表中定义的所有属性。通过继承，您可以使用父表选择所有个人资料，并使用就业资料表选择所有就业资料。如果通用配置文件仅使用公共属性，则可以将它们存储到父表中。
postgres 中继承的主要缺点是父表和子表不共享相同的命名空间。您无法创建跨越所有表的唯一约束。这意味着您必须以其他方式确保标识符是全局唯一的，例如为配置文件标识符保留一个单独的表。
您应该考虑继承的缺点是否对您的情况很重要。然而，如果您使用 postgres，这是为所有配置文件类型创建单独的表的明智方法，因为您不必重复公共属性的定义。
键值存储
您还可以为常见配置文件属性创建一个表，并将其余属性保存在（配置文件、属性、值）元组中。通过这样做，您将放弃 RDBMS 的优势，并且必须在程序中实现所有逻辑。不要这样做。
Here's a couple of options
All in the same table
In this option all the fields are always present whatever type the profile is. This is too easy to do the first time around as you only have to list all the columns. However, this is really bad design that will make your life harder in the long run, because the maintainability and extendability is poor. You should read up on database normal forms etc. Don't do this.
Master profile table and profile type dependent details on their own tables
In this option you will create a table for all profiles. This will include all the common attributes. This table will make sure the identifiers are all in the same namespace and each profile has a unique id. For each profile type you'll create a new table that has a foreign key reference to the master profile table. You can then select all employment profiles using an inner join on the employment profile table and the master profile table. This design allows you to create constraints for each profile type. Furthermore, this design lets you have profiles that are both employment and education profiles. You should probably do this.
Inheritance
Postges provides a facility for table inheritance. You can use this by creating a base table for all profile types and then creating child tables for each profile type. Each profile type then inherits all the attributes defined in the parent table. With inheritance you can select all profiles using the parent table and all employment profiles using the employment profile table. If generic profiles use only common attributes, they can be stored to the parent table.
The main disadvantage of inheritance in postgres is that parent table and the child tables do not share the same namespace. You cannot create a unique constraint that spans all the tables. This means that you have to make sure that the identifiers are globally unique some other way e.g. keeping a separate table for the profile identifiers.
You should think if the disadvantages of inheritance matter in you situation. However, this is the sensible way of doing separate tables for all profile types if you are using postgres as you don't have to duplicate the definitions of the common attributes.
Key-value store
You could also create a table for common profile attributes and keep the rest of the attribues in (profile, attribute, value)-tuples. By doing this, you'd discard the benefits of a RDBMS and you'd have to implement all the logic in you program. Don't do this.
PostgreSQL 支持表级继承。您可以将 Profile 表作为具有公共属性的父表，然后将教育和就业的子表仅包含特定于这些类别的属性
查看 PostgreSQL 文档 此处。
PostgreSQL supports table level inheritance. You can make a Profile table as the parent table with common attributes and then separate child tables for education and employment with only attributes specific to those categories
Check out the PostgreSQL documentation here.