Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mhillary
Starting Member
2 Posts |
 Posted - 2007-10-26 : 08:04:20
|
| Hi Everyone,I'm attempting to design a schema for a project I working on. The idea is that I can store a list of documents and associate meta data with them.The problem is that I don't know what that meta data will be at design time. The user will create the meta data fields in the software. For the first design I just had a table called meta field that contained the meta field defintion, and another table called itemdata that contained the document primary key, the metafield primary key and a value encoded as a varchar. This works fine while the list of documents is small.Another idea I've thought of is to have seperate tables for all of the metafields. This would allow me to be very specific about the values that can be stored in that column etc. It also turns out that for the kind of queries I'm doing its about 4 times as fast. The only problem is that the software will need to create these tables at run time. What I would like to know is, is this a really bad idea? If so why?. Also are there any other ways I can store this kind of data? The kind of queries I am doing are find all the documents where metafield 1 is A and metafield 2 is B and metafield 3 is C and metafield 4 is D etc. Cheers,Mark |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-10-28 : 18:42:46
|
| You're on a slippery slope to a well established anti-pattern here. You are going to try to store non-relational data in a relational database and will pay the resulting price if you are not careful.You should avoid stuffing everything into a string. The compares get very messy and your data becomes hard to analyse. Also, your queries become harder and slower as the number of columns increase.Secondly, I'd avoid creating tables for each type. The fact you are suggesting it means you clearly are not going to be able to use stored procedures (bad) and will have to use loads of dynamic SQL (also bad).The most common way I've seen of solving this is to have a bunch of extra columns for each document and map the meta data onto them (e.g. extradate1, extradate2, extraint1, extraint2 etc). Of course this does limit the number and type of metadata.Another way is to escape the relational thing and use free text searching or XML.Good luck. |
 |
|
|
mhillary
Starting Member
2 Posts |
Posted - 2007-10-29 : 04:14:29
|
| Hi,One of the reasons I thought of creating the tables at runtime is that it allows me to define the column type specifically for the data type I'm going to store. I've looked at XML, but the problem there is it will severally impact the performance of the system. For example if the user creates a tree view using 4 meta data fields in a system with 100,000 documents. Then I have 100,000 xml documents to parse, and 4 meta fields to search for in those documents. Thanks |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-10-29 : 18:40:41
|
| Creating tables at runtime is pretty much off limits when you have a proper design. In any case, you would only ever need one table per type. However, you still run into the issue of when your queries go across multiple attributes then your queries get out of hand very quickly.You need to get the database to interpret and query the XML for you. That's what it's for. I was not suggesting you store XML, extract and parse it! You can look up the XML features of SQL Server. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
|
|
|
|
|
Modelling Entities that are unknown at design time