[RESOLVED] Allowing Users to Delete?

I created a CMS using php/mysql.

To set up my question...

Table 1: How_Heard_About_Us
Users can enter in fields like "Newspaper", "Radio", "TV", etc.

Table 2: Appointments
Users enter appt information including the ID as a foreign key from the How_Heard_About_Us table.

I then have a report that finds all appointments and joins How_Heard_About_Us, kinda like this:

Code:

---

SELECT * FROM Appointments a
LEFT JOIN How_Heard_About_Us h
ON a.ID = h.ID

---

I have an "Admin" area to my CRM, where users can view the How_Heard_About_Us categories. I allow users to update/delete/add new categories which show up in a dropdown menu on the appts form.

My question is:

Is it a bad idea to allow users to DELETE the how heard categories if they are tied to existing appointments?

Should I somehow restrict the user from deleting the categories that exist in the appointments table?

Not sure what's best practice...

I'd say it's up to you, and depends on how valuable that data is. Assuming no other dependencies, the consequence of deleting the How_Heard data is simply that you'll no longer know it. Does that matter to you?

Well, I guess I'd just have to make a note to someone who is trying to delete "Newspaper", for instance, that any dependencies will be lost. Adding another new category called "Newspaper" won't re-assign previous lost dependencies.

If the link is broken, then the Appointments column called How_Heard_ID will contain values that won't join to the How_Heard_About_Us table. So I guess that isn't a big deal, right?

Thanks.

... not a big deal because I am using LEFT JOIN and not INNER JOIN to bring those tables together.

Quote:

---

... not a big deal because I am using LEFT JOIN and not INNER JOIN to bring those tables together.

---

Exactly; the left join won't break, it just won't get anything from How_Heard. So you just have an empty field.

from a usability stand point, it's probably not a big deal... from a data mining/historical perspective... it could be a big deal... if it were me... I wouldn't allow things like that to be deleted... but I'd allow them to be deactivated... the user makign the selection can then only see active options... but for historical/reporting purposes, the data is still there....

-tg

Quote:

---

Originally Posted by techgnome

from a usability stand point, it's probably not a big deal... from a data mining/historical perspective... it could be a big deal... if it were me... I wouldn't allow things like that to be deleted... but I'd allow them to be deactivated... the user makign the selection can then only see active options... but for historical/reporting purposes, the data is still there....

-tg

---

Thats exactly what I was going to suggest. Or another option would be a soft delete, similar to deactivating it from the end user, do not show to either end user or admin. It will still exist in the database but not show up in the admin area or to end users. So then its still there for reporting purposes like tg said.

I see. Well, if I allow the users to make the categories Inactive, which I could, then what would happen in a situation where a user adds a category by mistake/adds the wrong category? That's really the main reason I added the delete option.

I do have a form allowing users to update their categories. If they added a wrong category, they would just have to rename it. That logical?

then you let them disable it... or you only allow it to be deleted IF there's nothing else tied to it...

-tg

Ok. I am going to allow them to delete if nothing is tied to it. Otherwise they could only disable the categories.

I have a ton of work to do on that - I have multiple administration options other than How Heard categories.

Thanks guys.

Thanks for the advice. I got this sorted now.

Users can delete the records if they are not tied to anything else. Otherwise, they can make them inactive.

Thanks!