The Old Joel on Software Forum: Part 3 (of 5) - On design: database row/table depends?

On design: database row/table depends?

I have a database whose tables and rows have multiple dependencies between them. Certain table rows have columns that are keys into foreign tables.

Now, my UI is able to edit/delete these rows and tables. Of course, it exposes higher level concepts to the user, not rows and tables.

The trouble comes from the nature of these depends. Sometimes I want to delete a row when all the rows refering it are gone. Other times I want to prevent the removal of a row when someone is referring it. Etc.

Any ideas on designing this? My draft uses a reference counting system plus an observer mechanism. But it's quite complex and feels yucky.

Seems like a problem many other people should've encountered. Maybe a better framework emerged?

Nick
Wednesday, December 10, 2003

Integrity constraints?

Wednesday, December 10, 2003

That would certanly work at the database level. But what about in-memory?

My database is not so smart and I can't change that. Certainly I can't add integrity contraints. So I have to implement them in memory, when the data is being manipulated by the user.

Nick
Wednesday, December 10, 2003

>Certainly I can't add integrity contraints. So I have to >implement them in memory.

Don't. Referential integrity is one of those areas that have zillions of pitfalls and gotchas. Get a proper database.

What database are you currently using? ... if you are on a no-budget deal, you can get several open source databases that implements referential integrity.

Patrik
Wednesday, December 10, 2003

Can't. The database is already created, in production and outside of my control. It is currently edited by hand, row by row.

My task is to create a nice high-level UI on top of that, an UI that will abstract the actual rows and tables. But I have to keep them consistent...

Nick
Wednesday, December 10, 2003

This is why bug sharp knives were invented. Use one to either a) prise the lid off the database, or b) prise the top off the head of whoever developed the database.

Seriously, if it is a db problem (i.e. it must be consistent but relies on human beings knowing the internals of the db in order to keep it i order) then the db should be fixed. Can't you "suggest" to them that their problem can be sorted by a little downtime?

Wednesday, December 10, 2003

Again, not an option. The db is from a different company and it has to stay in this format for interoperability.

And I am NOT a db exper, but I don't think this kind of requirements can be solved at db level.

For (a completely fictional) example, let's say that I have a table with people's pics.

These pics are created in the Person UI, which also creates the Person table. Then these pics can be ref'd from various document tables.

Now, when I delete that person, I don't necessarily want the pic to be deleted too. I only want the pic row to go away when the last reference (from the Person table or Document table) to it goes away too.

How can I solve this at the db level?

Nick
Wednesday, December 10, 2003

What is the bloody database? Is it commertial or homegrown?

This detail would either allow people to make specific suggetstions or not waste time suggesting things that are not applicable.

njkayaker
Wednesday, December 10, 2003

"How can I solve this at the db level?"

This entirely depends on the DB, which is why everyone keeps asking you to say what bloody db it is. In virtually any modern database (modern being "within 7 years") there are foreign keys that impose constraints on deletes, and often additionally cascade deletes if that's your desire.

Dennis Forbes
Wednesday, December 10, 2003

Okay, if you can't hack the database and are unwilling to hack the people responsible for the db into little bits I would suggest placing a specific set of classes/objects/libraries between your app and the db. In there implement just the integrity rules that you want, nothing else. Then at least it is localised and if you need to write another app your rules will still be in place. You are going to have to do all that nasty wórk of select-users-relating-to-a-picture-and-if-there-aren't-any-remove-the-picture type work yourself so at least make sure you only have to write the code once.

How you inform the app of this is another question, and any answer depends on what the implementation technology is (it will be very different for example if your client is web based or whether it is a "normal" desktop application). TBH unless we know the whole details I doubt we could help much.

Wednesday, December 10, 2003

Whoah there! Unless I am misunderstanding your problem, it seems rather simple to deal with. When the user wants to delete a record from the UI, have it run a query against the "depends" table to see what other records the secondary record is referencing. If it is used by any records other than the one to be deleted, than delete the secondary record(s) also. Otherwise, leave 'em.
Hope that helps.

Jordan Lev
Wednesday, December 10, 2003

Well, I am *not* sure what the db is. I am accessing it through an sql library. I think it's a modified? version of Microsoft's JET engine.

Nick
Wednesday, December 10, 2003

So basically you're looking for a design pattern to help you enforce referential integrity in your app?

<BOFH>
Screw it, implement your app with no R.I. and watch how fast the DB operators put in R.I. at the DB-level *evil grin*
</BOFH>

Richard P
Wednesday, December 10, 2003

Hmmmm....a simple solution would be not to actually delete the rows...just set a 'to be deleted' flag and keep chugging along. The UI ofcourse (or the query building the UI) has to be smart enought to throw away those records marked as 'to be deleted'

Then at some convenient time like midnight or something when the db is not being actively used you could fire up a process which switches the database to single user mode and which walks through all the records marked 'to be deleted' and does a proper deletion of all the other references if they are not being referred to. Easy to do this in a batch process. The first pass will gather stats maybe to a temporary table and the second will use the temporary table stats to do the actual deletion....and delete the temp table finally.

Code Monkey
Wednesday, December 10, 2003

It must be a pretty old version of Jet if it doesn't have referential integrity. Every version I know does.

Contact the DBA and aks him about the exact version. Frankly if it doesn't have referential integrity it needs rewriting, full stop. Anything else is stupid.

Stephen Jones
Thursday, December 11, 2003

If you were an automotive engineer and told that you needed a way to slow the car down by engine braking because nobody had put any brakes in, what would you do?

Stephen Jones
Thursday, December 11, 2003

Are you sure your DB doesn't have integrity constraints? Try creating one thru that database engine.

Otherwise, you'll just have to hardcode the logic you want. I don't think it's worth it writing your own integrity checking framework for this since this does not seem to be a very large project you're working on anyway.

Gamut
Friday, December 12, 2003