Database Normalization - Can one field point to another?

Question

This may sound like a dumb question, but I am a software developer new to database design... So the concept makes sense to me, but maybe this doesn't carry--

Is it possible for a field in a record in Table B to simply point to a field in a record in Table A?

Example: If I have two tables... one containing a list of all employees and another containing a history of those employees coming and going from the office.

Employees
    ID
    First Name
    Last Name

History
    Event ID
    Event Time
    Event Type (Entry/Exit)
    EmployeeID

I can establish a relationship between History.EmployeeID and Employees.ID with a foreign key, but that duplicates data. So then History.EmployeeID and Employees.ID would contain the same ID number which would be stored in memory twice. So if I went into the database and changed John Smith's employee ID, I would need to write a script or something to scour the database and do a find/replace for that ID.

What I want is for Employees.ID to contain the real ID and History.EmployeeID to simply contain a pointer to Employees.ID. That way, if I updated John Smith's ID, the change would be centralized to one field in one table.

Is this possible?

Thanks

Answer 1

Even if SQL were to support a "pointer" like you describe, the pointer itself would take some memory. Ironically, the pointer might even be larger than the integer value it points to.

You tagged this question with MySQL, so I'll answer with respect to MySQL: there is no support for this pointer concept. In a relational database, columns contain values, not pointers.

Constraints are part of the table's metadata, and serve to make sure the value in the foreign key column does not get out of sync with the key column it references. Either it block changes, or duplicates changes if you declare the foreign key constraint with cascading effects.

Other implementations of RDBMS might have this feature, but not MySQL.

Answer 2

Using the same terms as you did, History.EmployeeID is already a pointer to the employee record, but so is Employees.ID. Both allow your application to locate the corresponding record. Generally speaking, physical records in a database do not necessarily maintain their physical location, so the use of physcial pointers, if they are available at all (e.g. Oracle's ROWID), is discouraged and should be limited to some very special cases.

In addition to that, table primary keys are usually chosen so that they don't normally change, so the use case you're providing is moot.

Answer 3

This is really a fundamental conceptual question concerning the basics of the relational model of data. Your desire to learn how this really works is admirable, and people should tolerate dumb questions for that reason. Unfortunately this doesn't lend itself to the kind of short, crisp Q&A that this site is really good at.

A major idea in the relational model of data, starting in 1970, was to get rid of pointers embedded in data records. The reasons why embedded pointers were a problem is too deep to go into here, but you can read about it in introductory books on database. CJ Date is a good author, and there are many others. Foreign keys do the work that pointers do in non relational databases.

A foreign key duplicates data, and this is intentional. If you don't want foreign keys, then you don't want a relational database, and you don't want to use SQL.

Other responders have told you how to join data by specifying a match condition between History.EmployeeID and Employee.ID. They have also told you how to get around update synch problems using cascaded update. I would offer the general opinion, in addition, that making ID's mutable is to be avoided, when possible.

What the other responders may not have mentioned is the role played by indexes and the optimizer in making a whole relational DBMS work, and work pretty well, thank you.

Indexes provide a mapping between key values and pointers. These mappings are used by the optimizer to come up with a strategy for retrievals and joins that can be hundreds of times faster than brute force scans of both tables. It doesn't matter much when the tables are small, but it matters a lot when the tables get big. Again, a book on databases will teach you how indexes come into being, and how they are kept up to date.

Non relational databases, that use pointers instead of indexes, can be made to run faster, for certain limited purposes, than relational databases. These non relational databases are good for speed, but they are very limited when it comes to making multiple uses, in different contexts, of the same data. They are also very limited when it comes to adapting the data structures to changing requirements. If you don't need to do any of this, then maybe you don't need a relational database.

Some database systems have been developed since the heyday of relational databases that are even more flexible than relational databases, at a cost of introducing even more overhead than indexes and optimizers do.

This answer just scratches the surface of what I think you are really asking.

Answer 4

Employee.ID should be an auto generated field (auto_increment) called Employee.EmployeeID, and it's value only set by the database system. History.EmployeeID would be a foreign key referencing Employee.EmployeeID by having the same value. You can use bigint as a datatype and it should be enough for most use cases.

What you want to have is a second row Employee.HumanID, which stores the ID of your Employee in business terms, for example a string that says "EmpID-007x". You can then easily change the HumanID of an Employee without affecting the integrity of the foreign key constraints.

RDBMS normalization is not about saving space, it's about removing redundancy. You can imagine all history events with the same ID being attached to the same Employee with this ID.

The big advantage of SQL compared to document based DBs link MongoDB is, that you can easily do queries like:

SELECT * FROM EMPLOYEE LEFT JOIN HISTORY USING (EMPLOYEEID)

And the JOIN this with another table and so on. Also note, that I instinctively used EMPLOYEE in the singular.

Another consideration would be to use HISTORY, or better ATTENDANCE as a list of time spans, having both a start and an end date. You would need to create the end date as infinity or something when you create the Attendance entry, but it is a lot easier to process things like working hours, just do a:

SELECT (ENDTIME - STARTTIME) FROM ATTENDANCE.

Answer 5

Id values should persist for the lifetime of the record. Maybe you're confusing an ID with something more flexible, like an Employee Number. Employee Number values may change (although rarely), but ID values are house-keeping data that you shouldn't ever change.

In fact, the PK should be a auto-number primary-key index, such that the numbers are never recycled and no record's number ever changes. You can think of a primary key ID as the identity of the record.

By way of real life example, as you grow older, your facial features change, and your ears get bigger, and your hair turns silver, but you never lose your identity, the very core of your physical self.

Similarly, the point of having a PK is to have an unchanging reference to that record-/ the record's identity. Changing the ID on the record should only occur under unusual circumstances. Like a database migration. The PK is best used when it is immutable.

Answer 6

If at some point in time you will need to update the ID field of Employee, it means it should not be aprimary key but an attribute of the Employee entity. It is much better to have an "abstract" key and reference that on the "History" table

Employees
    employee_key
    ID <-- Now, this is just an attribute. You may specify unique constraint if you want to enforce for uniqueness in the db
    First Name
    Last Name

History
    Event ID
    Event Time
    Event Type (Entry/Exit)
    employee_key <-- Foreign key to Employee