我在这里使用了一个非常简单的示例，请询问您是否需要更多上下文。
我正在重组/规范化数据库，其中大多数表中的ID字段具有主键字段，这些字段是自动递增的数字ID（1,2,3等），我想我需要将ID字段从数值更改为从行中的数据生成的字符串值。
我的理由如下：
我有5张桌子; 员工，会员，志愿者，实习生和学生; 所有这些都有数字ID。
我有另一个名为BuildingAttendance表，它记录了人们访问该处所时出于何种原因而具有以下相关字段：
ID Type Premises Attended区分员工和成员。 我使用类型字段，使用MEM作为成员，使用STA作为工作人员等。例如：
ID Type Premises Attended 1 MEM Building A 27/6/15 1 STA Building A 27/6/15 2 STU Building B 27/6/15我认为使用类似于以下内容的ID可能是更好的设计设计：
ID Premises Attended MEM1 Building A 27/6/15 STA1 Building A 27/6/15 STU2 Building B 27/6/15处理这个问题的最佳方法是什么？ 我知道如果我的主键是一个字符串，我的查询性能可能会受到影响，但这比2列更容易吗？
tl; dr - 我应该如何处理引用具有相同ID系统的其他表的记录的表？
I'm using a very stripped down example here so please ask if you need more context.
I'm in the process of restructuring/normalising a database where the ID fields in the majority of the tables have primary key fields which are auto-incremented numerical ID's (1,2,3 etc.) and I'm thinking I need to change the ID field from a numerical value to a string value generated from data in the row.
My reasoning for this is as follows:
I have 5 tables; Staff, Members, Volunteers, Interns and Students; all of these have numeric ID's.
I have another table called BuildingAttendance which logs when people visited the premises and for what reason which has the following relevant fields:
ID Type Premises AttendedTo differentiate between staff and members. I use the type field, using MEM for member and STA for staff, etc. So as an example:
ID Type Premises Attended 1 MEM Building A 27/6/15 1 STA Building A 27/6/15 2 STU Building B 27/6/15I'm thinking it might be a better design design to use an ID similar to the following:
ID Premises Attended MEM1 Building A 27/6/15 STA1 Building A 27/6/15 STU2 Building B 27/6/15What would be the best way to deal with this? I know that if my primary key is a string my query performance may take a hit, but is this easier than having 2 columns?
tl;dr - How should I deal a table that references records from other tables with the same ID system?
最满意答案
自动递增的数字ID比字符串有几个优点：
它们更容易实现。 为了生成字符串（如您所愿），您需要实现触发器或计算列。 它们占用固定的存储量（可能是4个字节），因此它们在数据记录和索引中更有效。 它们允许成员在不影响密钥的情况下在类型之间进行更改。您面临的问题是您有超类型的子类型。 此信息应与人员一起存储，而不是存储在出勤记录中（除非每次访问都可以更改其类型）。 在SQL中有几种方法可以解决这个问题，没有一种方法可以像编程语言中的简单类继承一样干净。
一种技术是将所有数据放在一个名为Persons表中。 这将有一个唯一的id，类型和五个表中的所有列。 问题是子表中的列非常不同。
在这种情况下，请使用一个名为people的表，该表具有唯一的主键和公共列。 然后为每个表分别使用表，并使用PersonId作为这些表的主键。
这种方法的优点是你可以为像Persons的东西提供外键引用。 并且，您还可以在适当的情况下为其他表提供对每个子类型的外键引用。
Auto-incremented numeric ids have several advantages over strings:
They are easier to implement. In order to generate the strings (as you want them), you would need to implement a trigger or computed column. They occupy a fixed amount of storage (probably 4 bytes), so they are more efficient in the data record and in indexes. They allow members to change between types, without affecting the key.The problem that you are facing is that you have subtypes of a supertype. This information should be stored with the person, not in the attendance record (unless a person could change their type with each visit). There are several ways to approach this in SQL, none as clean as simple class inheritance in a programming language.
One technique is to put all the data in a single table called something like Persons. This would have a unique id, a type, and all the columns from your five tables. The problem is when the columns from your subtables are very different.
In that case, have a table called persons with a unique primary key and the common columns. Then have separate tables for each one and use the PersonId as the primary key for these tables.
The advantage to this approach is that you can have a foreign key reference to Persons for something like BuildingAttendance. And, you can also have foreign key references to each of the subtypes, for other tables where appropriate.
数字ID与字符串ID(Numeric IDs vs. String IDs)我在这里使用了一个非常简单的示例，请询问您是否需要更多上下文。
我正在重组/规范化数据库，其中大多数表中的ID字段具有主键字段，这些字段是自动递增的数字ID（1,2,3等），我想我需要将ID字段从数值更改为从行中的数据生成的字符串值。
我的理由如下：
我有5张桌子; 员工，会员，志愿者，实习生和学生; 所有这些都有数字ID。
我有另一个名为BuildingAttendance表，它记录了人们访问该处所时出于何种原因而具有以下相关字段：
ID Type Premises Attended区分员工和成员。 我使用类型字段，使用MEM作为成员，使用STA作为工作人员等。例如：
ID Type Premises Attended 1 MEM Building A 27/6/15 1 STA Building A 27/6/15 2 STU Building B 27/6/15我认为使用类似于以下内容的ID可能是更好的设计设计：
ID Premises Attended MEM1 Building A 27/6/15 STA1 Building A 27/6/15 STU2 Building B 27/6/15处理这个问题的最佳方法是什么？ 我知道如果我的主键是一个字符串，我的查询性能可能会受到影响，但这比2列更容易吗？
tl; dr - 我应该如何处理引用具有相同ID系统的其他表的记录的表？
I'm using a very stripped down example here so please ask if you need more context.
I'm in the process of restructuring/normalising a database where the ID fields in the majority of the tables have primary key fields which are auto-incremented numerical ID's (1,2,3 etc.) and I'm thinking I need to change the ID field from a numerical value to a string value generated from data in the row.
My reasoning for this is as follows:
I have 5 tables; Staff, Members, Volunteers, Interns and Students; all of these have numeric ID's.
I have another table called BuildingAttendance which logs when people visited the premises and for what reason which has the following relevant fields:
ID Type Premises AttendedTo differentiate between staff and members. I use the type field, using MEM for member and STA for staff, etc. So as an example:
ID Type Premises Attended 1 MEM Building A 27/6/15 1 STA Building A 27/6/15 2 STU Building B 27/6/15I'm thinking it might be a better design design to use an ID similar to the following:
ID Premises Attended MEM1 Building A 27/6/15 STA1 Building A 27/6/15 STU2 Building B 27/6/15What would be the best way to deal with this? I know that if my primary key is a string my query performance may take a hit, but is this easier than having 2 columns?
tl;dr - How should I deal a table that references records from other tables with the same ID system?
最满意答案
自动递增的数字ID比字符串有几个优点：
它们更容易实现。 为了生成字符串（如您所愿），您需要实现触发器或计算列。 它们占用固定的存储量（可能是4个字节），因此它们在数据记录和索引中更有效。 它们允许成员在不影响密钥的情况下在类型之间进行更改。您面临的问题是您有超类型的子类型。 此信息应与人员一起存储，而不是存储在出勤记录中（除非每次访问都可以更改其类型）。 在SQL中有几种方法可以解决这个问题，没有一种方法可以像编程语言中的简单类继承一样干净。
一种技术是将所有数据放在一个名为Persons表中。 这将有一个唯一的id，类型和五个表中的所有列。 问题是子表中的列非常不同。
在这种情况下，请使用一个名为people的表，该表具有唯一的主键和公共列。 然后为每个表分别使用表，并使用PersonId作为这些表的主键。
这种方法的优点是你可以为像Persons的东西提供外键引用。 并且，您还可以在适当的情况下为其他表提供对每个子类型的外键引用。
Auto-incremented numeric ids have several advantages over strings:
They are easier to implement. In order to generate the strings (as you want them), you would need to implement a trigger or computed column. They occupy a fixed amount of storage (probably 4 bytes), so they are more efficient in the data record and in indexes. They allow members to change between types, without affecting the key.The problem that you are facing is that you have subtypes of a supertype. This information should be stored with the person, not in the attendance record (unless a person could change their type with each visit). There are several ways to approach this in SQL, none as clean as simple class inheritance in a programming language.
One technique is to put all the data in a single table called something like Persons. This would have a unique id, a type, and all the columns from your five tables. The problem is when the columns from your subtables are very different.
In that case, have a table called persons with a unique primary key and the common columns. Then have separate tables for each one and use the PersonId as the primary key for these tables.
The advantage to this approach is that you can have a foreign key reference to Persons for something like BuildingAttendance. And, you can also have foreign key references to each of the subtypes, for other tables where appropriate.
发布评论