Hey all, first post so be kind to this newbie
I'm working on a database for a small .NET member-based website, and am storing my data in an Access DB. I have a quick question on using calculated fields, and am not sure if this is a "calculated fields" issue or not, but maybe someone can help.
The site I am working on uses a messaging system to allow users to message one another (among other things).
My tables are laid out like this:

tblContactInfo
AccountUsername
FirstName
LastName
Gender
DateOfBirth
etc...

tblAccountInfo
AccountUsername
AccountPassword
AccountType
etc...

tblMessage
MessageID
MessageFrom
MessageTo
MessageDate
MessageSubject
etc...

Now in this database the tables are linked with relationships, with MessageTo being a foreign key to AccountUsername in the tblAccountInfo table.
Now picture this, when a user sends a message using the system, it is retrieved by another user. Just say however that an administrator decides to completely delete one of the two users from the database, the message would be left with a dangling reference because of the way the relationship is set up. If I set up Referential Integrity on the names however, the reference is deleted, which leaves me with a problem, the message is deleted, but the recipient loses the message completely. SO, if the sender is deleted, all of the messages they have sent are removed as well. (with the way it was set up, if the recipient is deleted, the message is deleted, but if the sender is deleted, the message remains with a dangling reference, and thus refuses to list properly when the user views messages on the site. I have since changed the databast to use a sort of calculated field, holding a non-relational "name" instead of an ID)

With that said, I'm looking for the best way to do this to maintain consistency, nullify dangling references, and if possible, maintain a best-practice solution.

I'm currently using what I believe to be a calculated field, instead of placing the sending users Username in the "MessageFrom" field, I'm creating a string of their first and last names (Lastname, Firstname) and placing that in the field. I'm just not sure if this is the best way to do it because of the use of the calculated field, it does however remove the dependency of the message on the user's information, and seems to work quite well.

I'm just a little confused as to whether there is a better way to go about doing this, should users perhaps never be deleted from a database once they are inserted? Or should I be using a "Lastname, Firstname" field the way I am, or is there a better way? I've been slowly trudging through books and websites but haven't yet come to a conclusion, perhaps one of the db pro's on this site can help?

Sorry for the horribly long message, I hope it wasn't confusing.
Thanks for you help ahead of time!

I would insert the name of the sender into the corresponding message field, without foreign key constraints. Instead I would enforce the constraint that the sender must be a valid user at the time of sending the message.

Should the sender be deleted at a later date and the recipient has not deleted the message, the name of the sender is still availible. If additional details are required then those fields can be added in addition to name.

Thanks a lot r123456,
That's how I have it set up currently, I just recently changed it because I realized (a little later than I should have) the issues I came across.
I was hoping this would be a good solution.
Is this a good practice to maintain for situations where users might be removed from the database and where data is sensitive to names, etc.? I'm also curious to know a good way to keep track of changes to accounts, say for instance a message is sent, then the sender modifies their account information to change their name, in our current situation, the name would not be changed on the message, unless I perhaps also included the username as a non-key item in the message row, and do a check for that when a user updates their information, and adjust the messages accordingly if they contain that particular username?
There's probably much better ways to go about what I'm doing, but if you have any more advice I'd love to hear it!
Thanks a lot for your reply.

Why delete the users at all? Presumably their records will take up less space than all their messages anyway. You could just flag them as non-current.

I like andrewst concept. Data is often cheaper to keep than to throw away, 'cause inevitably once its gone you find some reason to wish you had it back.

Flag users as "current" or "noncurrent", and then if somebody returns they can hav their old data back. If space becomes an issue, you could run a process that delete all correspondence where both the sender and the receiver are noncurrent. Problem is, then you can't run historical reports on usage patterns...

blindman

Hey Guys
Thanks a lot for the advice. Sounds like exactly what I needed.
I probably won't make that change in this project, because it's a relatively small project and is in the finishing stages (love that).
But I do have another somewhat large project starting up this week that will have similar features, and I will definitely keep this in mind.

Gotta tell you, it's great to post on a forum where people reply, can't tell you how many times I've posted on forums and have received no reply, but tons of reads.

Take care all.