为相同的表/类设计 SQL 表

Question

在我的代码中，我区分了 5 种类型的属性，这些属性通过从一个基类"ProjectAttribute"创建 5 个类来"描述"项目。一个项目可以有 0 到 x(所以在 SQL 中是多对多(的每种类型的属性。
这里有一些伪代码：

public abstract class ProjectAttribute
{
public int ID { get; set; }
public string Title { get; set; }
}
public class Attribute_HW : ProjectAttribute
{
public static List<Attribute_HW> Hardware_Attributes { get; private set; }
static Attribute_HW() 
{
// Read Attributes from external DataSource to List 'Hardware_Attributes'
}
// Some Attribute specific logic but no more fields
}
// Followed by 4 more Attribute Classes
public class Project
{
public ICollection<Attribute_HW> AttributesHardware { get; set; }
public ICollection<Attribute_SW> AttributesSoftware { get; set; }
// 3 more Attribute Collections
}

每个类都有一组不同的数据集，我想将其存储在 SQL 数据库中。但是为这样的相同类创建 5 个表感觉有点错误，这将导致另外 5 个链接表将其链接到另一个表(多对多(。最后我想出了2个不同的想法

想法 1我创建了 5 个表
和 5 个链接表以将其链接到"项目"表。
这感觉是错误的，因为每个属性表将包含大约 20-30 个条目，我必须查询 5 个不同的链接表来解决这个"混乱"。

想法 2
我创建 1 个名为"ProjectAttrbiutes"的表，并为每个属性类型添加一列。 即"IsSWAttribute"，"IsHardwareAttrobite"。此外，我只添加一个链接表。
听起来更好，但是否建议将原始数据结构缩减回一个表中？
另一方面，我最终只会有一个链接表进行查询。

我希望你能为我指出正确的方向。

Answer 1

我将为此目的使用 4 个表。

包含项目的表。

包含属性的表。

包含属性类型的表。

链接项目和属性的表

[项目] -> [项目属性] <- [属性]

-> [属性类型]

对于属性类型，MySQL和PgSQL中有很好的类型，枚举类型。在MS SQL中，您可以构建一个常规表。

SQL 小提琴

MS SQL Server 2017 架构设置：

CREATE TABLE project
(
id INT IDENTITY PRIMARY KEY NOT NULL,
name VARCHAR(255)
);
CREATE TABLE attributeType
(
id INT IDENTITY PRIMARY KEY NOT NULL,
name VARCHAR(255)
);
CREATE TABLE attribute
(
id INT IDENTITY PRIMARY KEY NOT NULL,
title VARCHAR(255),
type_id INT,
CONSTRAINT FK_attributeType FOREIGN KEY (type_id)
REFERENCES attributeType(id)
);
CREATE TABLE ProjectAttribute
(
project_id INT NOT NULL,
attribute_id INT NOT NULL,
CONSTRAINT FK_project FOREIGN KEY (project_id)
REFERENCES project(id),
CONSTRAINT FK_attribute FOREIGN KEY (attribute_id)
REFERENCES attribute(id),
CONSTRAINT pk_ProjectAttribute PRIMARY KEY (project_id, attribute_id)
);
INSERT INTO project (name) VALUES ('project 1 with hardware and software');
INSERT INTO project (name) VALUES ('project 2 with hardware only');
INSERT INTO project (name) VALUES ('project 3 with software only');
INSERT INTO attributeType (name) VALUES ('Hardware');
INSERT INTO attributeType (name) VALUES ('Software');
INSERT INTO attribute (title, type_id) VALUES ('Some hardware 1', 1);
INSERT INTO attribute (title, type_id) VALUES ('Some hardware 2', 1);
INSERT INTO attribute (title, type_id) VALUES ('Some software 1', 2);
INSERT INTO attribute (title, type_id) VALUES ('Some software 2', 2);
INSERT INTO attribute (title, type_id) VALUES ('Some hardware 3', 1);
INSERT INTO ProjectAttribute VALUES (1, 1);
INSERT INTO ProjectAttribute VALUES (1, 2);
INSERT INTO ProjectAttribute VALUES (1, 3);
INSERT INTO ProjectAttribute VALUES (1, 4);
INSERT INTO ProjectAttribute VALUES (2, 1);
INSERT INTO ProjectAttribute VALUES (2, 5);
INSERT INTO ProjectAttribute VALUES (3, 3);

查询 1：

SELECT * FROM project p
INNER JOIN ProjectAttribute pa
ON p.id = pa.project_id
INNER JOIN attribute a
ON a.id = pa.attribute_id
INNER JOIN attributeType t
ON t.id = a.type_id

结果：

| id |                                 name | project_id | attribute_id | id |           title | type_id | id |     name |
|----|--------------------------------------|------------|--------------|----|-----------------|---------|----|----------|
|  1 | project 1 with hardware and software |          1 |            1 |  1 | Some hardware 1 |       1 |  1 | Hardware |
|  1 | project 1 with hardware and software |          1 |            2 |  2 | Some hardware 2 |       1 |  1 | Hardware |
|  1 | project 1 with hardware and software |          1 |            3 |  3 | Some software 1 |       2 |  2 | Software |
|  1 | project 1 with hardware and software |          1 |            4 |  4 | Some software 2 |       2 |  2 | Software |
|  2 |         project 2 with hardware only |          2 |            1 |  1 | Some hardware 1 |       1 |  1 | Hardware |
|  2 |         project 2 with hardware only |          2 |            5 |  5 | Some hardware 3 |       1 |  1 | Hardware |
|  3 |         project 3 with software only |          3 |            3 |  3 | Some software 1 |       2 |  2 | Software |

Answer 2

这是我的一个表建议(类型是一个枚举，如{硬件，软件，...} - 这意味着MS SQL中带有id，Type的类型表(：

类型：标识、类型 属性：标识、标题、类型 ID Project_Attributes：Project_Id、属性 Id(多对多的桥接表( 项目： ID， 标题， ...

实际上一张图片胜过千言万语，即使是在短时间内编写的这段代码(所以需要经过一番思考后进行修改(也会提供更多的想法。这是代码优先模型，因此您可以运行并让它为您创建表：

string defaultConString = 
@"server=.SQLExpress;Database=SampleDb;Trusted_Connection=yes;";
void Main()
{
CreateData();
// check db state
ListData();
// Done with sample database. Delete.
// new SampleContext(defaultConString).Database.Delete();
}
private void CreateData()
{
var db = new SampleContext(defaultConString);
if (db.Database.CreateIfNotExists())
{
// Create some types
var tHW = new Type { Name = "Hardware" };
var tSW = new Type { Name = "Software" };
var tFW = new Type { Name = "Firmware" };
db.Types.AddRange(new Type[] {tHW,tSW,tFW});
db.SaveChanges();
// create some Attributes using types above
var a1 = new Attribute
{
Name = "Macbook Pro",
Type = tHW,
AttrProperties = new List<AttrProperty> {
new AttrProperty{ Description=@"15"" retina" },
new AttrProperty{ Description=@"i9-8950HK" },
new AttrProperty{ Description=@"32Gb DDR4 RAM" },
new AttrProperty{ Description=@"512Gb SSD" },
new AttrProperty{ Description=@"AMD Radeon Pro 555" },
new AttrProperty{ Description=@"OSX Mojave 10.14" },
}
};
var a2 = new Attribute
{
Name = "iMac",
Type = tHW,
AttrProperties = new List<AttrProperty> {
new AttrProperty{ Description=@"27"" retina 5K" },
new AttrProperty{ Description=@"i5 3.4Ghz" },
new AttrProperty{ Description=@"40Gb DDR4 RAM" },
new AttrProperty{ Description=@"1Tb Fusion" },
new AttrProperty{ Description=@"AMD Radeon Pro 570" },
new AttrProperty{ Description=@"OSX Mojave 10.14" },
}
};
var a3 = new Attribute
{
Name = "PC",
Type = tHW,
AttrProperties = new List<AttrProperty> {
new AttrProperty{ Description=@"AMD Ryzen Threadripper 1950x" },
new AttrProperty{ Description=@"64Gb RAM" },
new AttrProperty{ Description=@"1 Tb 7400 RPM" },
new AttrProperty{ Description=@"512Gb M2 mSATA" },
new AttrProperty{ Description=@"AMD Radeon Pro 570" },
new AttrProperty{ Description=@"Linux-Debian 9.5" },
}
};
var a4 = new Attribute
{
Name = "Database",
Type = tSW,
AttrProperties = new List<AttrProperty> {
new AttrProperty{ Description=@"postgreSQL" },
new AttrProperty{ Description=@"11 (beta)" },
}
};
var a5 = new Attribute
{
Name = "SomeROM",
Type = tFW,
AttrProperties = new List<AttrProperty> {
new AttrProperty{ Description=@"SomeROM update" },
new AttrProperty{ Description=@"Some version" },
}
};
db.Attributes.AddRange(new Attribute[] {a1,a2,a3,a4,a5});
// Some projects using those
var p1 = new Project
{
Name = "P1",
StartDate = new DateTime(2018, 1, 1),
Attributes = new List<Attribute>() { a1, a2, a4 }
};
var p2 = new Project
{
Name = "P2",
StartDate = new DateTime(2018, 1, 1),
Attributes = new List<Attribute>() { a1, a3, a5 }
};
var p3 = new Project
{
Name = "P3",
StartDate = new DateTime(2018, 1, 1),
Attributes = new List<Attribute>() { a2, a3, a4, a5 }
};
db.Projects.AddRange(new Project[] { p1,p2,p3 });
db.SaveChanges();
}
}
private void ListData()
{
var db = new SampleContext(defaultConString);
//  db.Database.Log =Console.Write;
foreach (var p in db.Projects.Include("Attributes").ToList())
{
Console.WriteLine($"Project {p.Name}, started on {p.StartDate}. Has Attributes:");
foreach (var a in p.Attributes)
{
Console.WriteLine($"t{a.Name} [{a.Type.Name}] ({string.Join(",",a.AttrProperties.Select(ap => ap.Description))})");
}
}
}
public class Type
{
public int TypeId { get; set; }
public string Name { get; set; }
public virtual List<Attribute> Attributes { get; set; }
public Type()
{
Attributes = new List<Attribute>();
}
}
public class Attribute
{
public int AttributeId { get; set; }
public string Name { get; set; }
public int TypeId { get; set; }
public virtual Type Type { get; set; }
public virtual List<Project> Projects { get; set; }
public virtual List<AttrProperty> AttrProperties { get; set; }
public Attribute()
{
Projects = new List<Project>();
}
}
public class AttrProperty
{ 
public int AttrPropertyId { get; set; }
public string Description { get; set; }
public virtual Attribute Attribute {get;set;}
}
public class Project
{
public int ProjectId { get; set; }
public string Name { get; set; }
public DateTime StartDate { get; set; }
public virtual List<Attribute> Attributes {get;set;}
public Project()
{
Attributes = new List<Attribute>();
}
}

public class SampleContext : DbContext
{
public SampleContext(string connectionString) : base(connectionString) { }
public DbSet<Type> Types { get; set; }
public DbSet<Attribute> Attributes { get; set; }
public DbSet<Project> Projects { get; set; }
}

如果这不是一个多用户大项目，那么看看LiteDB。这可能正是您正在寻找的，如果不是几分钟，也可以在几个小时内学会。