Denormalizing data and C # objects
I am using DB table with various different objects. This means I cannot have an arbitrary number of fields in it to store all kinds of different objects. Instead, I want to keep only the most important fields (dates, reference ids - the kind of foreign key for various other tables, the most important text fields, etc.) and an additional text field in which I would like to store more complete object data.
the most obvious solution would be to use strings XML
and store them. The second most obvious choice would be JSON
, which is usually shorter and probably also faster to serialize / deserialize ... And probably faster as well. But is it really? My objects also don't have to be strictly serializable because the JsonSerializer can usually serialize anything. Even anonymous objects that can be used here as well.
What would be the best solution to solve this problem?
Additional Information
My DB is heavily normalized and I'm using Entity Framework, but in order to have external super-fast full text search functionality I sacrifice bitward denormalization. For information only, I am using SphinxSE over MySql. Sphinx will return row ids, which I would use to quickly query my index-optimized conglomerate table to get the most important data from it much faster than querying multiple tables across my entire DB.
My table will have columns like:
-
RowID
(autoincrement) -
EntityID
(from the actual object, but not directly linked because that should point to different tables) -
EntityType
(so I could get the actual entity if needed) -
DateAdded
(note the timestamp when it was added to this table) -
Title
-
Metadata
(serialized data specific to a specific type of object)
This table will be indexed using the SPHINX indexer. When I search for data using this indexer, I would provide a series EntityIDs
and a limit date. The indexer would have to return a very limited number of computed ones RowIDs
, ordered DateAdded
(descending). I would just join these RowIDs
to my table and get the appropriate results. Thus, it won't actually be a full-text search, but a search filter. Retrieving RowIDs
would be very fast, and retrieving results from a table would be much faster than comparisons EntityIDs
and DateAdded
comparisons, although they would be indexed correctly.
a source to share
The only way to store data in a SQL database that won't cause long-term pain is to actually create a correct, normalized indexed schema, and extend that schema as needed when adding new properties to domain objects.
Please do not try to "serialize" objects in the SQL database. If this is really what you want to do, you are better off using an object database like db4o .
Update:
This is how I currently understand the problem space based on comments and question updates:
- The table structure has already been normalized;
- A full-text engine (Sphinx) is used to optimize certain search queries;
- The specific "serialized" data discussed here will be used as summaries or previews for individual search results and does not represent complete object data.
My choice for this, in order of preference, would be:
-
Use FTS engine functions.
Nearly every FTS engine, including Sphinx, allows custom attributes to be stored as part of each "document". You are currently saying that you are only storing the row id to join this table. Your results will return much faster if you don't join at all, and instead store this information inside the full-text index itself. There are some pretty tight limits on what you can put here, but if you can work around the limit, this is your best bet. -
Documented database.
You say you don't even use the "Full-Text" part of Sphinx, you just use it to optimize certain queries. Why not cut out the middle man then? You suggested JSON as the serialization format; MongoDB (to mention just one) supports BSON natively. You can still create indexes on shared columns, but unlike mysql, it does understand BSON format and can store that data much more efficiently than JSON or XML string in a relational database. If you denormalize anyway, you have the freedom to choose whatever repository you want; choose the one that is best optimized for your specific requirement. -
Inheritance with one table.
This is a general design that overrides normalization for ease of comparison. In your case, the whole goal is denormalization, so this is a good deal. It's not a good option if there are hundreds of columns, but for 10 or 20 it will be fine, it will store your data as "data" and shouldn't impact performance in any significant way. -
XML columns.
The advantage of this approach is that the data is not opaque. It actually makes sense in the context of a database. If you have to store this information in a mysql database - presumably you expect some special queries to run - then you can save it in a format that mysql can actually understand. On the other hand, if you are 100% sure that you will never need to "deserialize" this data until it gets into your application, I will probably go with ... -
Custom binary serialization format.
If you must store data in your mysql database and , you know for sure that you need to never index it or even read its contents from a query, then donโt waste precious I / O on bloated text encoding. Even JSON is bloated compared to binary because JSON has to store all property names; if you are doing your own serialization, you can use one or two bytes to define the type and then deserialize the remaining fields / properties in a known order. Just data, no metadata.
I wouldn't even use .NETBinaryFormatter
here, I would create my own highly optimized version. After all, it should be fast fast! and every extra byte that goes into your table makes the query slower. You can even GZip some data, depending on what's in there.
If I haven't fully understood your requirements yet, I wouldn't even consider other options.
a source to share
Do not do this. It is a bad idea.
If you really have to do this, I would use XML. SQL Server, for example, allows you to query XMl as long as the field is an XML type.
In a sense, you are doing what object oriented databases do. They came out of dislike because, for the most part, ORM tools like Hibernate and Microsoft Entity Framework allow you to have the best of OO and the relational worlds.
a source to share
I actually use text blobs in my DBMS all the time. When used for the right purpose, it can have a positive performance impact and save on the existence and maintenance of many tables and development time. Ideal for when you need to store frequently changing, non-relational string metadata.
Although I would only consider using text blocks for KVO objects (i.e. non-entity - objects that only make sense for the string in which it is stored). Also don't worry if you need to do any server side manipulation with it i.e. requests etc.
For those interested, I've developed a fast, robust Type Serializer that's perfect for storing blocks of text in a compact, human-readable text format.
a source to share