How should I store EAV address history in the database?

To simplify some开发者_开发百科what ... I'd like to have the history of user EAV data, specifically where it points to addresses, but eliminating redundancy seems tricky.

Addresses has its own table.

Users have a load of odd data in addition to standard data in the users table and in other entity tables. I was planning on putting this odd user data in an EAV table. This user EAV data can also include multiple entries that point to addresses stored in the addresses table.

I want to record the history of changes.

So when a user address changes what it seems like needs to happen is:

• A new address is added to the addresses table.
• The old EAV entry now instead of pointing to the old address row is updated to point to the new row in the addresses table.
• The change to the EAV row is written to an EAV history table (will use a trigger)

To be honest that doesn't seem like much work but:

• Should the old entry in the addresses table sit there forever as read-only? (OK, so my guess is yes, with clearing out old records being a warehousing consideration, but it just seems so ... icky).
• How can I at least reuse those address entries? (Perhaps by offering matching existing entries upon new user input)

It all seems so unclean, and not just the EAV ;)

If you want to track the history of a person's address, then EAV may be a little to "elegant" a solution. I'm assuming that by EAV you mean that the address is broken into fields and each field is an attribute. Therefore, if someone moves, but stays in the same city their address line changes but their city, state and ZIP (possibly) stay the same.

People move all the time. I forgot the statistic, but it's in the neighbourhood of 16% or 18% of people move each year. There is often business reasons for having an audit history of people's address.

If you are tracking the history of addresses, I would suggest that you would be better served with keeping a snapshot based history table and using a trigger to populate that table. You could use a schema like this:

ADDRESS_HISTORY
  person_key  int
, change_date datetime
, address_1   nvarchar(50)
, address_2   nvarchar(50) null
, city        nvarchar(30) 
, state       nvarchar(2)
, zip         nvarchar(10)
, primary key (person_key, change_date)

Of course, the schema needs to mirror your own address attribute structure.

This doesn't have any elegant reuse, but it avoids all of the confusion that you are wrestling with that such reuse would naturally entail. Keeping a complete snapshot of each address will use a little extra space but it will make actually using the history much easier.