SQL circle reference

[brandonb]

I want to create 2 tables.

Table 1. Entity
- Name, Address, Phone Number.

Entity could be anybody, from a customer ordering information, or whatever. Entity just holds common info about people.

Table 2. User
- Username, Password, Foreign Key Entity

User are people logging into the system. They also have a foreign key to Entity table to record their Name, Address, and Phone Number.

That part is fine.

However, what I want to do is add a foreign key from Entity back to user. The reason why I want to do this is for auditing purposes. If someone adds an Entity, I want the username to be recorded in the Entity table of who added the Entity.

Is that just crappy design? If so what is a better method?

 

[KLin]

Sounds like you need a 3rd table to record entity transactions. Also, what fields make the relationship between the 2 tables? Name to Username?

 

[Markbnj]

Yeah, I agree with KLin. You can get away with circular relationships if you don't try to enforce them with constraints going in both directions, but they're messy. Making a third table to track updates also will give you the ability to keep track of more than one change per record.

 

[mcmilljb]

I'm curious what the relationships are too. I'm confused as to what is actually going on. Maybe if the OP defined the relationships and their roles, we could easily give some better ideas.

My one idea was using an ID number. It's static and unique, so no updating other tables for username changes.

 

[brandonb]

Originally posted by: mcmilljb
I'm curious what the relationships are too. I'm confused as to what is actually going on. Maybe if the OP defined the relationships and their roles, we could easily give some better ideas.

My one idea was using an ID number. It's static and unique, so no updating other tables for username changes.

Each table has an IDENTITY column (uid_entity, uid_user) which indicates a unique row. So and the relationship between them (foreign key would be to that identity column).

For example:


If user had the data:

uid_user........username.............password..........uid_entity
0...................syadmin..............secret................null
1...................brandonb.............joebob..............1

and Entity had the data:

uid_entity.......Name.................Address....................Phone Number.....uid_user...ts
1...................Brandon Bazoo....Minneapolis, MN.........612-111-1234......0.............2007-01-01
2...................Brandon Bazoo....Minneapolis, MN.........612-535-6938......1..............null


If that is setup, I can tell that syadmin actually setup the entity for the brandonb and then brandonb actually updated their own entity and changed the phone number, and the previous row when inactive on 2007-01-01 (thats when the update happened, indicated by the ts column in entity)... When that update occures, the system would update the uid_user = 1 row with the uid_entity = 2.

What i'm trying to achieve is the database which allows full auditing and time independence in realtime (being able to see the state of the database at any given time)

I was also thinking of linking the uid_entity + ts in the primary key (concatenated key) to prevent the update to the user table. So the active row would always be ts = null (or 0 or something) but the uid's would remain constant, but I'm thinking that could cause some serious performance issues later on as the uid's don't get added to the end of the clustered index and there would be alot of page splitting, etc.

I hope this helps answer some questions.

 

[mcmilljb]

You could also add a column for CreatedBy and LastUpdatedBy, using the uid_user as a foreign key.

Then you can search on the user id to find all entities created by that user or even last updated by the user. You don't need the uid_entity column in the User Table if you design it this way.