[haku]

I've got a question now, and probably more in the future, about databases. This week I've been putting together a new section for my company's website. I read recently somewhere that its better not to delete rows out of databases, but rather just add a column that registers the row as deleted or not. So I've been building the database this way, but the more I've been thinking about it, I'm wondering - is this actually good practice? Won't it clog up the database with unneeded entries over time?

Also I'm wondering about setting they type of database column, in mysql specifically. Does it really matter what you set the column type to? I was talking with a buddy a while ago, and he said there are advantages to setting every column to text. But I never really asked him why, and have wondered since. Does anybody have any ideas on this?

I thought this could be maybe a good practices for database thread. The only problem being that I don't really have anything to contribute except questions!

[Ben Abrams]

haku, on Jan 10 2008, 01:19 PM, said:

I read recently somewhere that its better not to delete rows out of databases, but rather just add a column that registers the row as deleted or not.

I guess during busy times, on a highly used table (like a topics table) this could save some overhead on the database, then deleting all posts with this set value in one swoop, rather then many little deletes. This is the theory, i dont know if its backed up.

As for the rest of your points, im not sure.

[Catalyst]

The only practical reason for not actually deleting rows that I can think of is that you might need an audit trail and want to still have a record of the data available if necessary, and I do this for certain projects. Otherwise I'd just do the deletes, it would take an enormous amount of them to make a difference since you'd have to process an UPDATE to mark the row for deleting the other way so it's still a SQL transaction being performed.

As to using strings for everything, it's going to make your database bigger and if you ever do more complex things with your database like user functions, stored procedures etc then you're going to have to do a lot of conversions to get them working the way you want. Also if you want to do SQL searches like date > 9/1/2005 or price < 50.00 then you're going to have to put more conversion code into your SQL. The benefit to it would be that you could stick anything in there, like a string where it's supposed to be a number, etc. so you won't get errors back from the database when you try to use it the wrong way. But I'd say that's a flawed approach to bug prevention.

[Karl Buckland]

Haku, I'd suggest that you've been given rather bad advice.

As Catalyst points out, the only good reason not to delete database rows is for an audit trail. I can't see how updating a row to change a value to show that the row is now 'deleted' would be any cheaper than actually deleting that row. You still need a write lock on the row, and in many situations the table. All it will do is keep data you probably don't want and slow down search queries - especially if every query you then make also needs to check that records aren't 'deleted' - imagine how much that could slow down complex queries?

And then there's using textfields for all string types. Again, Catalyst is completly right, but I want to hammer home how right he is. This is, in my opinion, a monumentally bad idea. If you need to filter records by date then you need to process each string to do so, instead of using a database's built-in date functions which adds quite a bit of additional overhead (or you have to get the database to convert the strings before using its functions). The same goes for numbers. I can't see any benefits.

[haku]

Thanks for the thoughts guys. Its good to hear some thoughts on how databases work. I know how to work with them, but I'm not at all clear enough on how things are working behind the scenes to be able to use them as effectively as they should be able to be used.

[smoseley]

Having a "deleted" flag is usually a bad idea for the reasons listed above.

However, if you're designing your database to 3rd normal form, you should use FK relationships with ON DELETE CASCADE triggers. If you're using MySQL, you'll have to use InnoDB for this.

If you are designing a transactional database (i.e. one that requires audit trails), you still shouldn't use a "deleted" flag. In that case, you should have a status history table following a pipeline model, in which you only perform INSERTs or SELECTs, which will not require locks, and will allow for a true stable enterprise system.

The only reason I use visibility flags is for reporting, in which case I want some of my data to be excluded, but want to avoid the expense of a JOIN in my query.


As for your other question, it's never best to use TEXT fields. INT fields are much better optimized for searching and index better. Don't use VARCHAR unless you need to, and only use TEXT for big chunks of data.

[EncoderDecoder]

actually, not deleting a record from the database but just flagging it as 'deleted' is now being considered as a good practice.. that's what is taught in our school now.. its because all records in the database are a good source of information and information is essential in business..