Numeric IDs vs. String IDs

Question

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    Attended

To 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/15

I'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/15

What 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?

Answer 1

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.

Answer 2

Gordon Linoff already provided an answer that points out the type/supertype issue. I refer to this a class/subclass, but that's just a difference in terminology.

There are two tags in this area that collect questions that relate to class/subclass. Here they are:

class-table-inheritance shared-primary-key

If you will look over the info tab for each of these tags, you'll see a brief outline. Plus the answers to the questions will help you with your case.

By creating a single table called Person, with an autonumber ID, you provide a handy way of referencing a person, regardless of that person's type. By making the staff, member, volunteer, student, and intern tables use a copy of this ID as their own ID you will facilitate whatever joins you need to perform.

The decision about whether to include type in attendance depends on whether you want to retrieve the data with the person's current type, or with the type the person had at the time of the attendance.