Comment "Flexible" database keys (Score 1) 356
What I've been planning to look into as a project is the following problem:
I have several instances of a platform, each with several customers. I'd like to be able to move customers around, but this would cause all kinds of problems with primary keys and foreign key relations in our database. To make matters worse, the database doesn't contain actual foreign keys all the foreign key relationships that are used and it contains a few text-fields which would require search-and-replace on some of the foreign keys.
I know of no standard solution to fix this, but it should be possible to do. It should be possible to make a tool, feed it with a configuration file that maps out all the relationships (ideally this could be generated from the database meta-data itself) and search&replace actions. Supply it with a list of "start" keys (in my example, this would be a customerid) from which it would scan all linked records. Have it export a file format which can be imported using the tool on a different database, generating new keys and properly setting foreign keys to the new keys. For bonus points, have it de-duplicate records as specified in that configuration file.
Such a tool would probably be very interresting to a lot of people.
Benefits of the project is that (AFAIK) it's not been done before, it's quite technical in nature (no need to understand non-IT stuff) and would produce code that would be useful after it is no longer a school project. Down side is that it's going to take quite some effort to make it truely effective. Also, as of now, you can't patent it