需要数据库表设计帮助

发布于 2024-10-26 16:27:51 字数 502 浏览 1 评论 0原文

CREATE TABLE MemberProfile
(
   memberID, 
   memberTypeID,
   aboutMeID,
   memberRegID,
   memberProfileSettingsID,
   lastLogin datetime,
   PRIMARY KEY(memberID)
)

memberTypeID 代表基本会员、黄金会员、白银会员等
aboutMeID 代表向人们介绍该用户的帖子/话题。每个用户都会有“关于我”页面。属性有：标题、正文等。
memberRegID 代表用户名、密码、电子邮件、接收通知、isActive 等
memberProfileSettingsID 代表选项例如隐藏收藏列表、隐藏年龄等

这样的设计好吗？我不想将所有字段放在一张表“MemberProfile”中。

原文

CREATE TABLE MemberProfile
(
   memberID, 
   memberTypeID,
   aboutMeID,
   memberRegID,
   memberProfileSettingsID,
   lastLogin datetime,
   PRIMARY KEY(memberID)
)

memberTypeID represents Basic Member, Gold Member, Silver Member, etc
aboutMeID represents a post/thread that would tell people about this user. Every user would have About Me page. Attributes are: title, body, etc.
memberRegID represents username, password, email, receive notification, isActive, etc
memberProfileSettingsID represents options such as hide favorite list, hide age, etc

Is this a good design? I didn't want to put all fields in one table "MemberProfile".

分享到QQ

分享到微博

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

发布评论

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

ι不睡觉的鱼゛ 2024-11-02 16:27:51

看起来您与 MemberID 和所有属性之间存在强制的 1:1 关系，这意味着您可以将它们全部存储在同一个表 (memberprofile) 中。这样做将使编码变得更加容易。

要知道，很难强制执行真正的 1:1 关系，但可以使用触发器来完成。查看带有 [database-design] 标签的问题。最近有两个关于1:1关系的问题。

我对标识符有一些评论。如果您使用其他表来进行成员设置、登录信息等，则应该从 MemberProfile 中删除键，而使用这些表中的 MemberID，并使用外键引用 MemberProfile。这将让您直接使用这些表，而无需加入会员配置文件。

就像这样：

create table memberprofile(
   member_id
  ,membertype_id
  ,primary key(member_id)
  ,foreign key(membertype_id) references membertype(membertype_id)
);

create table profile_settings(
   member_id
  ,some_setting
  ,other_setting
  ,primary key(member_id)
  ,foreign key(member_id) references memberprofile(member_id)
);

现在我想起来了，我认为 AboutMeID 不是 1:1。您的意思是链接到用户发布的论坛帖子来介绍自己吗？在这种情况下，基数为 1:0，因为您通常必须在创建帖子之前注册:)
您可以将其实现为 MemberProfile 中的 NULLable 列，或者实现为以 member_id 作为主键和论坛帖子 ID 的单独表。如果您期望大多数行最终都具有价值，我个人会选择可为空的列。

是否对 (1:1) 属性使用一个或多个表是一种实现选择。根据您的查询模式，它可能会影响性能好或坏。

It looks like you have a mandatory 1:1 relation with the MemberID and all of the attributes, which means that you could store them all in the same table (memberprofile). Doing so will make coding a lot easier.

Know that it is hard to enforce true 1:1 relations, but it can be done using triggers. Have a look at questions with the [database-design] tag. There are two recent questions on 1:1 relations.

I have some comments on the identifiers. If you go with additional tables for member settings,login information etcetera, you should drop the keys from MemberProfile, and instead use the MemberID in those tables, referencing MemberProfile with a foreign key. That will let you use those tables directly without joining to memberprofile.

Like this:

create table memberprofile(
   member_id
  ,membertype_id
  ,primary key(member_id)
  ,foreign key(membertype_id) references membertype(membertype_id)
);

create table profile_settings(
   member_id
  ,some_setting
  ,other_setting
  ,primary key(member_id)
  ,foreign key(member_id) references memberprofile(member_id)
);

Now that I'm thinking of it, I think AboutMeID isn't 1:1. Do you mean to link to a forum post made by the user to introduce himself? In that case, it is 1:0 cardinality, because you typically have to register before creating a post :)
You can implement this as a NULLable column in MemberProfile, or as a separate table with member_id as primary key and the ID of the forum post. If you expect most rows to eventually have value I would personally go with the nullable column.

Whether to use one or several tables for your (1:1) attributes is an implementation choice. Depending on your query patterns, it can impact performance good or bad.

回复收藏 0 原文