SQL中的多值表没有足够的列

Question

例如，当我有一个多值表，它是一个人会说的语言。比方说，这里有5列用于存储语言。

CREATE TABLE Languages  
(  
PersonID int primary key,  //this is FOREIGN KEY to a Person, "REFERENCES Person(ID)" for example
Lang1 varchar(30),  
Lang2 varchar(30),  
Lang3 varchar(30),  
Lang4 varchar(30),  
Lang5 varchar(30)  
)

如果有人会说5种以上的语言，解决方案是什么?

Answer 1

你的设计是坏的，不能处理你所描述的场景。你需要修改设计，这种情况下的正常设计是:

CREATE TABLE Person
(
PersonID int identity (1,1) primary key,
Forename varchar(128),
Surname varchar(128)
-- etc etc
)
CREATE TABLE Language
(
LanguageID int primary key,
[Name] varchar(128)
-- etc etc
)
CREATE TABLE PersonLanguage
(
-- Optional to have a specific PK for a linking table - I prefer to
PersonLanguageID int identity (1,1) primary key,
PersonID int foreign key reference Person (PersonId),
LanguageID int foreign key reference Language (LanguageId)
)

然后您可以添加任意多的语言，例如

-- First insert will give ID 1
insert into Person (Forename, Surname)
values ('Bob', 'Test');
-- Insert will give IDs 1-7
insert into Language ([Name])
values ('English'), ('Spanish'), ('French'), ('German'), ('Dutch'), ('Portuguese'), ('Greek');
-- Add as many languages as you like
insert into PersonLanguage (PersonId, LanguageId)
values (1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (1,7);