4

Context

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.

Database structure

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.


Question

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:

  1. the first is what I just told, one field identifying the table and one the key

  2. 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.

  3. 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.

  4. 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.

  5. 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

Improve this question
4
  • If the question is not clear please tell me, the basic issue is being able to refer from one record to one of several alternative tables
    – gab
    Mar 4, 2013 at 14:34
  • Could you clarify the section "database structure", perhaps quick sample? We do something related so think I understand what are asking, but want to verify first.
    – rlb
    Mar 20, 2013 at 8:37
  • @rib Serious problems were found and the project was trimmed down. <br/> Regarding the main issue anyhow I choose to make one table listing all the data ids with more specific tables referencing this.
    – gab
    Apr 9, 2013 at 14:42
  • 1
    It is unclear exactly what you are trying to achieve here. Is your concern that table names are hardcoded?
    – Thomas Kejser
    Sep 17, 2014 at 8:46

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.