poor table relationship design

[dsm2000]

Having a problem with a commercial software developer.


This is a karaoke hosting program.

I am posting this here in the hopes of either being vindicated or further chastised if I am wrong by people with more database knowledge than me.
My attempts to communicate the following problem led to this final single line email communication from him to me -

HIM: - You are an idiot.


(WOW - customer service sure has changed!)

On to the problem . . .

He recently updated his backend from stored xml files to an access db.


One table is the media table that lists all of the music files available including disc id, Artist, Title, media type and path to the file
a second table is the singer history table that lists all of the songs that each singer has ever sung including date sung,key change, tempo change and path to the file.

He has designed the tables to be linked on the path field in each table.
He has set the relationship as a one to many from the media table to the singer history table including cascade deletion. ie any record deleted in media table is automatically deleted in singer history table.


Some singers have over 300 songs in their history that have been entered over months and months
The idea that these records which are a completely different important data set other than sharing a common file path are deleted just to maintain referential integrity. Seems like very poor design to me.

My stance is that -
Singer history records are completely separate data that should not be deleted without any warning just because the song is no longer available in the media table. Media table should just have an ondelete routine that tags the record in the Singer History as inactive. That way the information is still there and available to be reactivated with a valid path at anytime in the future

The worst part is that these deletions can happen in bulk inside the program if the user goes to rebuild his media table. Without warning, months and months of built up singer history can just disappear.

Any thoughts on his current and/or my suggested structure?

[June7]

I am of the view that data should rarely be deleted. Why would an item be deleted from the media table? The product still exists. Maybe he threw his copy in the trash but others are still out there somewhere and another copy could be obtained. I agree with you. Have a field that indicates status of the item - such as: I own, I want, I trashed. Even if the item is no longer in possession the history might still be of interest.

So what is the problem with the commercial developer? Are they building this app for you?

[dsm2000]

It is an update to a commercial program I purchased a few years back.
He is a one man shop and his program is actually the best out there in many ways.
The problem is that he does not seem to try to stress test his updates with different systems, machines, data sets before releasing them into the wild and then gets defensive when something crashes in a real world scenario that he has missed.

This last major update with the conversion from the xml to mdb has been thru 12 minor version release fixes and still isn't 100% even for the way he designed it.
Still get MaxFileLocks exceeded errors when deleting a large folder of records from the media table(Another reason I hate Microsoft DI automation)

His reasoning for cascade deleting between these two unrelated sets of data is beyond me.

The mdb is not locked so I can easily fix the problem by removing the cascade delete in Access. I notified him just as a courtesy that other users probably would not appreciate losing their singer history without any warning and he called me an idiot.

His method of adding, editing, or deleting songs is terrible.
You open the media form
choose the drive or drives, and folder or folders where your songs reside. You place a check box next to each if you want that folder to be searched recursively.
You click update database and the program adds all the songs to the media db.

The problem is if you decide to move files to a different spot. If you had some songs temporarily on your C: drive and you now want to move them to a new drive or location.
You move the files to their new location via windows explorer
You open the media form
You click on the old folder location and hit remove
The program deletes all of the files from that folder currently in the media table and proceeds to also delete any of the singer history songs that were in that folder. No warning no caution just all gone.
You add the new folder location
you click update database

you now have an unknown number of songs that have disappeared from the singer history.

[June7]

So this is more than just data maintenance in a table, this is file management? I agree, just because folder location is changed is no reason to remove history.