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 |
|
MGA
Starting Member
28 Posts |
 Posted - 2011-09-19 : 19:10:04
|
| I'm working on a document management application(web-based), my question is how to handle the Metadata and Record Type Management? here is the requirements: each document has a set of metadata fields and a set of record types, The user can add multiple record types each record type Consists of a set of fields the user defines these fields and choose their types, so they are defined in a form of: fieldId, FieldName, Type(textbox, readio button, list of items, ...), size,... The document metadata is one set of fields that the user can add or remove fields to it, and it is defined in the form of (fieldId, FieldName, Type(textbox, radio button,..),...) the same as record types but these are one set of fields that are applicable to each document. there are also metadata data for the folders and they are one set.when the user add a document he is prompted to fill in the document metdata and choose from a set of record types that he will fill they with data in a later step.the fields of the record types and metadata fields are stored on a table that defines the structure of these fields, then the application will render these fields based on this structure (and this is a nother issue) but what about the data that will be stored in these fields, should i create a table for the document metadata and a table for each record type, then using Transact sql to add columns to the table ,alter tables and define each column data type based on the structure selected by the user and so on? or there is a better approach? Any better design because by this way it will by very tedious and very complecated to handle all these issues.and if there is any articles or books to get started with these requirements??thanks in advance. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2011-09-19 : 19:14:43
|
| from your explanation i'm assuming this isn't a very high transactional system.therefore i'd use an XML column to store your meta data. you can even use an xml schema to constrain the values.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.9 out!SQL Server MVP |
 |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2011-09-19 : 20:48:26
|
quote:
Originally posted by MGA
...the fields of the record types and metadata fields are stored on a table that defines the structure of these fields, then the application will render these fields based on this structure (and this is a nother issue) but what about the data that will be stored in these fields, should i create a table for the document metadata and a table for each record type, then using Transact sql to add columns to the table ,alter tables and define each column data type based on the structure selected by the user and so on? or there is a better approach? Any better design because by this way it will by very tedious and very complecated to handle all these issues.thanks in advance.
Not knowing much about the intricacies, the two main approaches (I see) in DB design are as you mention;Creating/Altering DB objects in runtime (i.e. table columns, etc. for data)Or storing data vertically and generically (with as much normalization as the model allows for).Both methods present their own implementation challenges. Permissions and privileges must be managed carefully in the presentation and middle tiers if the user is going to be altering DB objects in runtime and Both will require alot of Dynamic SQL and/or constant pivoting of data (either via spocs or middle tier query generation) for CRUD ops.Whereas metadata tables are usually fixed and constrained to your business need and framework design, the data itself can become obtuse and it is difficult to fix a table design when needs (column widths, datatypes, etc) are continually expanding on the whim of the application user.Long ago, I developed a metadata driven application for dynamic data entry forms generation, persistence and reporting using [the vertical method] for data storage and it was alot of extra work for adhoc querying and reporting. Datatypes required strong validation on the front end and data conversion via dynamic sql was always required due to the generic column datatype need [in a vertical approach].Once the framework was laid, the application did its thing very nicely. Generating forms dynamically from metadata contained with the DB and persisting and retrieving the values entered by the user. But (as any developer knows) the clients always want new requirements and always have changing needs, including adhoc/analytics/BI reports and new functionality.It was an interesting project in retrospect. I learned alot working on it.Best of luck with your solution. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-09-20 : 02:07:05
|
| I haven't looked through ehorns links but you should also look up the term "EAV-model" (entity-attribute-value). It's more or less just a different name for the metadata model you are describing but you will find a lot of information on this site and on the web by searching for "EAV".- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
|
|
|
|
|
Store the metadata and record type fields ?