Skip to main content
gamedev.net
🔒 Locked

Database Design Question

Started by kirkd Mar 30, 2011 at 12:29 AM 9 replies 1.7k views
Original Post
kirkd
kirkd
I'm trying to put together a database scheme that will allow importing data from multiple sources that may have different data content. Consider it this way - the application should allow users to import data from .csv files, for example, and I assume no duplicate rows (observations) between the two files. File one has 5 data columns, Data1, Data2, Data3, Data4, Data5. File two also has 5 data columns, Data1, Data2, Data6, Data7, Data8 - notice that two of them are the same and 3 are different compared to File one. Also, I do not know what data the files will have before hand. Just to make it that much more complicated, let's assume that Data3 in table one and Data6 in table two actually describe the same type of data, but they call it by a different name, so Data3 and Data6 are synonyms. Again, I don't necessarily know the nature of synonyms before the data is imported.

Below is how I've considered it thus far, with each Table name listed with its column names listed.

DataSources (obvious, store details of the data sources here)
---------------
SourceID_PK
Acquisition_Date
Acquired_From


DataFields (This would represent the accepted field names for all the imported data - the accepted synonyms - a sort of controlled vocabulary)
------------
FieldName_PK


DataFieldSynonyms
-----------------------
DataFieldSynonym_PK
DataSource:SourceID (link to data source table)
DataFields:FieldName (link to DataFields table)


So far, I think this captures what I need to manage synonyms. In this simple case, DataFields would contain every field name except Data6. Data6 would be the non-accepted synonym, but we want to keep it around so we know what file number 2 called this field. DataFieldSynonyms would contain every data field ever encountered. One minor redundancy here is this: In this example Data3 from File one is the accepted synonym for Data6 from File two. Let's say we have File three that also has Data6 but for whatever reason it is synonymous with Data1 in that case. Twisted I know. The result I see is that Data6 is listed three times in the DataFieldSynonyms table, once from each Data Source. For Data Source 1, it is liked the the accepted Data3, for Data Source 2, it is linked to the accepted Data3, but for Data Source 3 it is linked to Data1. If you're still with me, I'm interested if that redundancy will matter, and if so, how might I eliminate it.

Now it starts to get tricky - storing the actual data. Most simply, if we assume all the data is the same type, I could use a very tall, skinny table.

DataTable
-----------
Observation_ID_PK (primary key)
DataFields:FieldName_FK (link to DataFields table)
Value

But would this become a performance issue once I start collecting all the data for a particular observation or if I try to reconstitute the original input file?

Even more complicated is the situation where I have different data types that I need to treat differently: boolean, real, integer, categorical. Here I could have 4 tables like DataTable, one for each data type - BooleanData, RealData, IntegerData, CategoricalData, each with the same three fields as above. Alternatively, I could have a wider, sparse table:

DataTable
------------
Observation_ID_PK
DataType (use a simple flag to tell me which of the 4 data types is being used)
DataFields:FieldName_FK (link to DataFields table)
Boolean_Value
Real_Value
Integer_Value
Categorical_Value

But again, it is really just a tall, skinny table. If I have 1 million rows in each input file, and each row has 100 columns of data, will a tall, skinny table become a major issue once I start trying to collect data. My concern is that I end up with lots of self-joins on this DataTable.

Any tips are greatly appreciated.


-Kirk
WavyVirus
WavyVirus
The performance implications of the various possible designs which might satisfy these requirements are a bit too lengthy to cover in any depth in a forum post.

Can I point you in the direction of a couple of AskTom questions on similar topics (If you're not familiar, Tom Kyte is Oracle's "database evangelist" and one of the most well-respected DBAs/database developers in the industry. He's been answering user-submitted questions for many years now, and although the site is Oracle-centric there's plenty of good information which applies to RDBMSs in general):

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056

http://asktom.oracle.com/pls/asktom/f?p=100:11:1674767412776281::::P11_QUESTION_ID:3361148209692




The design you're talking about is essentially an Entity-Attribute-Value (EAV) model. Although such a model is completely generic and flexible, it generally has poor query performance as "reconstructing" all of the data associated with an entity (datasource or observation in your case) becomes overly complicated.

I would suggest that you think about either creating tables with the proper data-types, or having a wide table with potentially unused columns.

ApochPiQ
ApochPiQ
As WavyVirus noted, this isn't really the strong point of a relational database. It would be very useful if you could expand on the exact nature of what you're trying to do any why, so we could possibly offer some alternatives.

For instance, there exist some really decent key-value libraries that allow you to do exactly what you need without the overhead and tradeoffs of doing so in a relational model.

It may also ultimately make the most sense to just bite the bullet and write converters that stream the data from the source files into a uniform representation internally that fits relational data modeling techniques, or whatever other representation method you settle on.
kirkd
kirkd
Thank you both for the comments. As I suspected, it is ideally flexible and fits my criteria, but it will suffer from complexity once I start trying to recapture the data.

As for the real nature of what I'm doing, here goes - just remember - you asked. 8^)

I work in the field of Chemical Informatics, and I've developed a method to visualize very large collections of chemical compounds in a chemically intuitive way using a self-organizing map. It isn't a problem to do what I need if I'm starting with a standard set of compounds, but there are many, many vendors, and new ones emerge all the time. One goal would be to look at multiple collections of compounds from different vendors and determine their degree of overlap or complementarity. Another goal would be to run the compounds through the self-organizing map and then project a property related to the various compounds onto that map in hope of revealing structure-property relationships. All of this is easy enough, but getting the compounds from different vendors to play nice with each other is the trick.

Regarding synonyms, different vendors call the same property by potentially different names. The easiest example for this is molecular weight. It is called MolWt, MW, Molecular Weight, MolMass, etc. There are other properties like this as well. I think the method by which I'm accounting for synonyms will work and will not be too complicated.

The real problem lies in that different files of compounds from different vendors will have different properties that I can't foresee. Also, I may want to include compounds from other sources - NIH, WHO, different companies - and there may be a whole variety of unknown properties associated with these - activity in an arbitrary biological system, any number of chemical properties or computable properties. I don't know up front what types of properties I'll see, what they'll be, how they will or won't map to existing synonyms, etc. With the number of compounds possibly extending into the 10s or 100s of millions, I need a place to store them so that my tools can work on them.

The tool I have now works very well and has gotten some public usage, so I would like to make it available for others to use. Having a workable database scheme that the user can interact with through some simple (ish) dialogs - property mapping and selection, etc. - is a critical function. At this point, I just haven't come up with a good solution for how to store the data a user wants to work with knowing that I don't actually know the nature of that data.

Thinking about it further, maybe the wide, sparse table would be the best choice. The problem there would be that the data table wouldn't be static. Any time a new set of compounds was brought in with new data, I'd have to add columns to the table. That would be done programatically, so not a problem, but I might need to consider what would happen if I exceed the column limit. Any compound that lacked data for any particular property would just have no entry for that column. Any comments on that option?
ApochPiQ
ApochPiQ
Again, this sounds like a case where the right tool for the job could make all the difference; relational databases aren't really phenomenal at storing sparse data grids. Semi-structured data approaches will have a lot of benefits over a relational model here. Something like XML, JSON, YAML, etc. would be a great place to start.

In fact I would go so far as to recommend that you look into storing all of your data in a unified XML format. Design a tool that allows people to convert existing data into your format, allowing for custom fields, and so on; then use XQuery or something similar to do your data mining. I've done something similar to this in the past (data format normalization) and it can be tricky to explain to people how best to make use of the tool, but once it's out there and documented, it's a pretty productive approach.
kirkd
kirkd
Interesting. You know, I've been so focused on making this a "database" that I couldn't see past the RDBMS concept. I assume if I had an XML structured "database" I could still query it as I've already done. The biggest issue I have is that to train the SOM, I can't hold all the data in memory at once. I've essentially set up a compound queue that holds around 100,000 compounds in memory, uses them for training, and then loads in the next 100,000. It allows me to train against a million structures without having the whole batch in memory. I assume I could do the same thing with an XML query engine.

Thanks! That changes the whole game.

-Kirk
TheTroll
TheTroll
I am going to make a couple of assumptions just because I have to in order to respond.

First assumption, you have a set of data that you are looking for from each data set.

Second assumption, the data will have most or all of the data you are looking for.


If this is not the case, then in the end it is a not a database issue because you can't really get there from here, with any decent performance.

So what can you do? Store the information you are looking for, and ignore the rest. So how do you do that? Well you are not going to like it, but the "Best way" (in my view), is to write a converter for each data file type you get. Now I am assuming there will be a limited number of different formats. You have to parse the data at some point, so might as well do it before you dump it into the db.

This means you are only storing data you care about in a format you can use the data.

One of the keys to DB development is only store the data you need and only store it once. Storing all that data you don't care about is just bloating your db for not real reason.

theTroll
kirkd
kirkd
Troll - Thanks for the comment, and yes, you make an excellent point. The problem is, I don't know if data is important at design time or not. In fact, I don't know what data will actually exist. Therein lies the problem. I cold make some fundamental assumptions, enforce those during database design, and presume a certain set of pre-existing data, but in my own experience, in a very short time, new data will come available that is considered important and will essentially break my assumptions.

The wide, sparse data table seems the most obvious solution, albeit not the most friendly. If I start off with a table that has columns for the data attached to file one, and then append new columns as new data from new files is encountered, that would essentially solve the flexibility problem. But, it seems it breaks the usability and performance problem in that I end up with an non-static design and a sparse table most likely won't be stored well.

Let's step outside the DB realm. One suggestion was an XML storage/query mechanism. This seems flexible enough, but I'm afraid it will be storage space intensive. Just to keep the discussion moving and dynamic, what are other options that you (or anyone out there) can think of?
kirkd
kirkd
Would it be just insane to take this approach? It seems a workable solution, but (obviously) my experience here is limited. I can only claim ignorance. I accept all criticism, I just ask that it be constructive. Be gentle. 8^)

I expect no more than 4 data types - boolean, integer, real, and categorical. (OK, so integer, categorical, and boolean may actually all be the same type).

Create 4 tables, one for each data type, that will have an ID column corresponding to any particular chemical structures, and a predefined maximum number of data columns, say 500. Columns are named Real001, Real002, Real003...Real500; Cat001, Cat002...Cat500; and so on. I would get this:

RealData
-----------
Observation_ID
Real001
Real002
...
Real500

Similar for CatData, BoolData, and IntData tables.

The synonyms table will now have an additional 2 columns which cross-reference from the accepted name and which data table/column it belongs.

DataFieldSynonyms
-----------------------
DataFieldSynonym_PK
DataSource:SourceID (link to data source table)
DataFields:FieldName (link to DataFields table)
DataTable
DataColumn

Other pieces being the same:

DataFields (This would represent the accepted field names for all the imported data - the accepted synonyms - a sort of controlled vocabulary)
------------
FieldName_PK


DataSources (obvious, store details of the data sources here)
---------------
SourceID_PK
Acquisition_Date
Acquired_From


The storage itself wouldn't be very efficient due to the initial sparsity of the tables, but it might be better than a really wide table that is very sparse. It would tend to be left-packed initially, but that would eventually give way after more, new data is imported. There would also be a constraint on the maximum number of types of data that are allowed, but that may be tolerable. It might, however, behave itself for this case. And, referring back to my ignorance of the topic, it may just be a recipe for disaster. 8^)

-Kirk
ApochPiQ
ApochPiQ
My gut reaction is that that approach is begging for becoming unmaintainable in short order.

I'm curious; what gives you the impression that a semi-structured XML style approach would be inefficient in terms of storage space? From my perspective it's about as optimal as you can get; if on-disk space is really that big of a deal, chuck a compression algorithm at it and decompress the data into memory buffers when loading individual compounds/etc. from disk. (Or, in an ideal world, spend an extra $80 and throw another TB of disk at it...)

Parsed XML in a memory representation is fairly memory-light, all things considered, and can be made even less demanding with some cleverness when it comes time to do your cross-data searches. It's hard to be specific without having a real understanding of the chemistry side of what you're doing, but I can't see it being a real problem in that sense. More likely what you will have to worry about is user-facing issues, like how to get normalized data to begin with.
kirkd
kirkd

I'm curious; what gives you the impression that a semi-structured XML style approach would be inefficient in terms of storage space? From my perspective it's about as optimal as you can get; if on-disk space is really that big of a deal, chuck a compression algorithm at it and decompress the data into memory buffers when loading individual compounds/etc. from disk. (Or, in an ideal world, spend an extra $80 and throw another TB of disk at it...)


Actually I'm investigating that approach as a first alternative now. My thought is to try and make do with what I have for now while I transition to something better/more maintainable. At this point I just need to educate myself on the XML option. Having taken a look at XQuery, as you suggested, it looks quite viable, but its new and requires a little reworking of what I have. I guess I'm starting to split this into immediate, short term, and medium term solutions.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.

Sign in to reply to this topic.