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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-02-24 : 19:44:01
|
| Ian writes "I am in the process of writing a rather complex/large document management system. In it, I allow users to "index" their documents into something I call indexsets. These indexsets are able to be created by people with arbitrary fields and datatypes. So, for example, I could have a "Taxation" indexset, which might have fields such as Year (int), EmployeeID (lookup->external source), UserID, Comments, etc...Another indexset could be for company policies, etcNow, since the indexset's schema is able to be created & modified on the fly, I have two options..a) The way I have currently gone...I made several database tables.Indexsets - which has an ID and a name, etcIndexsetFields - stores a fieldID, fieldName, DataType, etc for each field of an indexsetThen I made tables such as FieldsInt, FieldsChar, FieldsDateTime, etc which have a FieldID, DocumentID and a value (the value is either int, char or datetime, depending on the table)I chose this way as I would not need to issue DDL commands from the application since with just a bit of DML, I can add a new record to the fields table and just keep going on with my inserts/updates since the Fields-DataType tables don't need their schema modified.b) I could issue DDL statements and actually make tables based on each Indexset - so I would still have Indexsets, IndexsetFIelds and then have IndexsetA, IndexsetB, etc which would have the correct columns.My overall goal is to satisfy the following requirements1) If going with (a), how would I make a view that simulated the tables in (b) - I saw one idea on your site here at the bottom (I wasn't aware that you could select from select statements in this way!) I could have a select statement select from several selects - don't know how fast this would be, and since there would often be where-clause limiting factors (either documentID X or saying "fieldA < 10 and fieldB <> 15", I wonder if the query optimiser would look at these before doing the selects... I have a graphical query builder tool for the end users - this tool would be MUCH easier to write with views or tables as in (b). This has caused my dilemma...2) To eventually support replication - I don't know if DDL replicates, and even if it did, I'd still have to write fully custom merge code since table schemas might change3) To make it easily searchable - I think that option (b) would make querying faster (which is the most common operation) but option (a) makes admin easier - since I wish to use stored procs and avoid dynamic SQL if possible, (b) would involve the admin process also recreating stored procs with the proper parameters.I use SQL server 2000 Standard with all service packs applied on a Win2k pro machine (SP3) for development. Deployed sites would have similar configuration.I'm not looking for an implementation, but rather heaps of ideas, criticisms (not too harsh!) and if (a) is the better way to go, then ideas regarding goal (1) - efficiently creating the views.Thank you for any ideas you guys can shed on this!IanPS - Great Site! Love the design section - very few sites have that... Also, VERY BIG SORRY for the length - I've spent hours of debate inside my head on this so there's a lot to spell out!" |
|
|
|
|
|
|
|
Dynamic Tables in App without resorting to DDL - some ideas...