modiagag

Good morning

In messages system i have table to store messages and another table to contain the links which the message is posted through

Now i want to delete a link but i don't want to delete the message sent through this link

The problem:

When displaying the message sent what will be fount in the link field (i.e for the deleted link)

I tried to move the deleted link data to separate table (EX: deletedLink) but if the user added new link with the same name as the deleted link The problem mainly in when displaying the message sent i will have the same link twice one for the deleted and the other for the added one.

If any one has a good idea for doing that please reply to me

I doní»t want the sql code

I want just the idea

Thanks

kind Regards

Mohammed Al Maghraby



Re: Modeling and Tools Database idea

bmains

Hey,

If you don't want the added link to contain the same name as one that was deleted, then you need to query and check to see if the name is taken in the deleted link table. If no results return, then you can add the link safely. Whether you do all of this in a stored procedure, or use application logic in your application, that is up to you.

But essentially, you query the deletedLink table to find the name being added, and if it exists, you avoid adding a record, but if it doesn't, you add it.





Re: Modeling and Tools Database idea

modiagag

First Thanks for interest

There is misunderstanding in the problem

I have to enable the user to a link with the same name because the original link supposed to be deleted

I.e. it doesn't appear in the forms of the application

So the user thinks that the link was deleted

Again

i want to delete the link because it became useless for me but at the same time there are message in the message table which are related to that link

And I don't want to delete them

Also at the same time after deleting the link the user wants to view a report about the messages which contain information about the link used for sending these messages

, after deleting the link Form where could I get the link information ...that is the problem

I hope the situation is clear

Again thanks for interest

Kind regards





Re: Modeling and Tools Database idea

bmains

Hey,

From what I see, this is often what a history table stores, so the deleted links/information would be there. Or, you could store the deleted link info in the same table, but you have another field that says logical delete, so any field with that set to one is considered deleted. And you could use that in an query or processing you may need.

Is that any closer





Re: Modeling and Tools Database idea

LivetoCodeCodetoLive!

I agree with BMains, I think it should be a history table too. You could also have 2 tables, one for links and the other for messages.

The messages table will have a message status and the view that you use to display the links should exclude the links with the status of "deleted". i.e. use soft delete as opposed to hard delete.

I hope this was helpful.






Re: Modeling and Tools Database idea

LeoTohill

Does the link have any properties other than a name, and perhaps a hidden key

If not, then as others have suggested you should not actually delete the link (don't delete the row.) Instead, just add a column named "Deleted" or "Inactive". Then if someone tries to add a link with the same name, you just reset this one back to active ("undelete it").

If, on the other hand, the link has other properties than can vary each time it is created, and you want to remember those old property values even after it is "deleted", then you can't simply undelete it and let the new use have different property values. Instead, you need to allow multiple instances (rows) with the same link name. Each row needs a unique primary key that is not the name. The messages will refer to the link using that primary key. Your application must allow only a single instnance with a given name to be active at any time.

Hope this helps,







Re: Modeling and Tools Database idea

Martin Platt

Mohammed,

Is there a reason for partititioning the tables into active and deleted tables, instead of just marking the record as deleted If there's a very large amount of data and performance is a real issue, this might be a fair choice, but generally speaking, I'd say not.

The way you explain to problem sounds like you're keying the data based on the link name, and selecting in the same way A surrogate key would mitigate the need to search two tables to see if you can create a link for a deleted item, and it wouldn't seem to make much sense from the user point of view, and could cause problems if two links have the same name, but are different links

Perhaps I'm misunderstanding the situation, but that would seem to be the solution to me, if you want to retain the deleted links.

Please let me know if I'm missing the point, and I'll try again! Smile

Cheers,

Martin.