用户表到用户和用户首选项.这是规范化的吗? [英] Users table to Users and User Preferences. Is this normalized?

问题描述

我有一个名为 Users 的表，其中包含越来越多的首选项.这些首选项可以包括 ReligionId(这将关闭包含宗教列表的另一个表).

I have a table called Users that has a growing list of preferences. These preferences could includes ReligionId (which would key off to another table that contains the list of religions).

偏好列表越来越多.我想将它从 Users 表中拆分为 2 个表.我认为行之有效的策略是创建一个名为 UserPreferences 的单独表.我想知道这样做是否符合规范化规则.这是一个插图，使事情更清楚一些.

The list of preferences is growing. I want to split it off the Users table into 2 tables. The strategy that I think would work well is to make a separate table called UserPreferences. I'm wondering if doing this is in line with the rules of normalization. Here is an illustration to make things a bit more clear.

这是规范化的吗?有更好的方法吗?感谢所有评论.

Is this normalized? Are there better ways? All comments appreciated.

UserPreferences 如何关闭其他表:

How UserPreferences key off to other tables:

推荐答案

您至少可以拥有用户和首选项.用户和偏好之间应该存在一对多的关系.一个用户可以有许多首选项.您还可以将电子邮件地址拆分到另一个表中 - 这样一个用户可以有多个电子邮件地址 - 您可以有一个标志来表示主要地址.DDL 看起来像:

At a minimum you could just have Users and Preferences. There should be a one to many relationship between users and preferences. One user can have many preferences. You could also split out the email addresses into another table - so that one user could have multiple email addresses - you can have a flag to denote the primary one. The DDL would look like:

    create table Users
    (
       UserId int,
       Age int
    )

    create table Preferences
    (
        PreferencesId int,
        UserId int,
        ReligionId int,
        PersonalDescription varchar(2000),
        HairColor int
    )

    create table EmailAddresses
    (
        EmailId int,
        UserId int,
        EmailAddress varchar(100),
        IsPrimary bit
    )

    create table Religion
    (
        ReligionId int,
        Name varchar(200)
    )

Insert into Religion (ReligionId, Name) Values (1, 'Jediism')
Insert into Religion (ReligionId, Name) Values (2, 'Sithism')
Insert into Religion (ReligionId, Name) Values (3, 'Yuuzhan Vong')
Insert into Religion (ReligionId, Name) Values (4, 'Pinacism')

Insert into Users (UserId, Age) Values (1, 30)
Insert into Users (UserId, Age) Values (2, 18)

Insert into Preferences (PreferencesId, UserId, ReligionId, PersonalDescription) values (1, 1, 1, 'a description')
Insert into Preferences (PreferencesId, UserId, ReligionId, PersonalDescription) values (2, 1, 4, 'another description')
Insert into Preferences (PreferencesId, UserId, ReligionId, PersonalDescription) values (3, 1, 4, 'even another description')

这篇关于用户表到用户和用户首选项.这是规范化的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！