The company I work for makes a number of different softwares each with its own database. As they're all about related things (accounting) a good amount of data is theoretically the same among several of them, and if the user owns more than one there are options to import the appropriate data from the others.
In general each database is "structured" differently, most tables use absolute positions and numbered fields (reflecting the software's data entry forms), some are much worse.
So the importing feature is not trivial.
At the moment the references to the locations of the data in the other softwares are hard-coded in the source code, but in order to facilitate both the importing and a future possible unification I'm making a database of them.
I had the feeling that other systems such as rdf could be more suited to the task but I had to do it with an Access database. I think the matter is problematic for any sql databases though.
The database I made is divided in data definitions and associations, under certain conditions, of a location (in some cases a cell in an absolute position, in others a field) of a database's software with a datum or class of data.
I decided to make a table in the database for each general type of data, with fields indicating its properties to identify an exact specific datum (or class of data if some field is left null), except for properties common to all data such as the period to which it refers. I might just make a table with each possible exact datum but it is a lot more convenient to be able to select based on the properties. The grouping of data is at a subjective most general reasonable level.
I will use code to operate on the various softwares' databases, I don't need to make cross-database sql queries.
The issue now is that a certain location in a target database may be associated with any one of the types of data, thus with any one of the data definition tables.
Of course in terms of information I'd just need a field identifying the table and one for the record ID, but as far as I can tell, both normal SQL queries and constraints can't apply to different tables based on the current contents of a cell. SQL queries could probably still be made putting a conditional join for every possible table, not very convenient, but I could actually also do without them and use only code. There seems to be no way to make foreign key constraints on the other hand, I could also do without them but I'd prefer not to.
I figured different alternatives to face the problem, each with serious disadvantages, I'd like you to tell me if you know any better method or what you think would be the least worst:
the first is what I just told, one field identifying the table and one the key
I might make one foreign key field for each table. SQL queries would still be very complex but I would have referential integrity. The big problem though is that I have a limit of 255 fields and it is very likely that if not now at a certain time there will be more than 255 types of data, and thus tables, that need to be referred to. I know I might make a child table with the same fields at that point, but the 255 fields limit applies also to views, so I would then need to change the queries and the code, or put now ahead of time a field and establish a convention for the child table name, and consider that in the code. I would like to allow the users to insert new kinds of data and make the associations so any solution should not require manual adjustment of the database and the code that refers to it at a future time.
One table for all the data types, with a lot of void cells. This is actually impossible, I would need way more than 255 fields. Unless of course I use here the child table approach.
Going back to the first option, with the single field for the indication of the table, I figured I could enforce referential integerity in one table by inserting a "table in the middle", listing all the possible specific data, with one record for every record in each table; the absence of refential integrity would be "pushed" to this table then, but it would be a little easier, with the requirement to be careful only when inserting a new datum, not every time I need to reference it. I will probably have 5 references to each datum on average though, so a relative improvement.
Finally, as every option has its drawbacks, why not go the whole hog and just make a single lookup table that is easier to manage and let the code do all the checks?
By the way, how can it be that it's not possible to refer tables in records? Wouldn't it just take adding a TableRef field type?
I hope I've made myself clear, thank you for your help
note this was previously posted at https://stackoverflow.com/questions/15192645/database-describing-databases-and-references-to-tables