Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
2  
I don't understand what your aim is. What are you trying to accomplish, that a FOREIGN KEY with ON UPDATE CASCADE doesn't already? –  ypercube Jun 20 at 19:31
    
However, if the DBMS wanted to move the row containing a given ID to a different location in the database, it would have to locate every row that contained a pointer and update that pointer, or leave a "forwarding address" at the old location, or break the linkage. –  Walter Mitty Jun 21 at 12:50
    
@njozwiak, the real issue here is that you are suggesting to mix the physical implementation details (i.e. to store a pointer to the physical location of a field) with the logical model (i.e. into the actual table's data.) This is in contrast with the relational model - which does not care at all about the physical implementation. –  ypercube Jun 21 at 14:05

6 Answers 6

up vote 5 down vote accepted

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.

share|improve this answer
    
I did tag MySQL because that's what I'm currently working with. And that answers the question, thank you. If anyone is aware of any other DBMS systems that support something like this I am definitely interested in reading up on them. (BTW I did not mean to imply that the field containing the pointer would not take any memory. It obviously would. At a minimum, enough to contain the address of the field it is pointing to.) –  njozwiak Jun 20 at 19:24
    
And how large would such a pointer need to be, given that it would have to identify the tablespace, the page, the row, and column it references? Hint: almost certainly larger than the value you're likely to be referencing. –  Bill Karwin Jun 20 at 19:33
1  
As BillKarwin, the pointer may have to be bigger than the value it points to. Another issue is that the pointer would have to be updated in some situations where the data remained unchanged but (for example) were moved to another disk or page, say for a table reindexing/rebuilding/reorganizing. –  ypercube Jun 20 at 19:37
    
Yes, but memory isn't the only consideration. If the only reference is matching data then horsepower needs to be applied to search the tables and until a match is found. A pointer would simply know where the data was at all times. Now, as I said, this is new territory for me. The amount of cycles necessary to match records might be trivial or maybe there is a design to speed up that process. I'd be interested in the mechanics of how it all works. –  njozwiak Jun 20 at 20:38
1  
As ypercube and mustaccio mentioned, a physical locator to the row is subject to change, as InnoDB moves rows around during updates. Besides, a foreign key typically references the primary key of the parent table, which is the clustered index of that table, and therefore pretty quick for lookups. –  Bill Karwin Jun 20 at 20:52

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.

share|improve this answer
    
Unless I misunderstand how FKs work, History.EmployeeID is NOT a pointer to Employees.ID. The relationship is established by containing identical table UNIQUE data. That is not a pointer. That's data matching. If I changed the Employee ID in the Employees table that would break the relationship between any History records of that employee. Also, a PK being 'normally' chosen doesn't moot the use-case. It might not have been a flawless analogy, but the question wasn't if it was. It's whether pointers between fields are possible. –  njozwiak Jun 20 at 19:07
2  
Like I said, physical locations of table rows and column values within them are not guaranteed to remain constant, so persistently storing pointers would have been useless, just like it would have been useless to save values of C pointers between program invocations and hope they reference the same data the next time you run the program. Record IDs play the role of pointers in databases, and it is for this reason that they should be immutable. Other redundancy scenarios should be taken care of by proper normalization. –  mustaccio Jun 20 at 20:40

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.

share|improve this answer

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.
share|improve this answer

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.

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.