locked
Tips on database design RRS feed

  • Question

  • Currently I've got a bunch of tables in my database (examples of tables are: employee, customer, ...) for my application. But now I want to make a sort of "form"-designer. With this designer the user will be able to create their own forms by adding fields and selecting a field type for this field. A field type could be text, a number, a date, but also an employee, a customer and so on.

    Now is my question: how would I best store the value of a field? My first guess was I should store all values as text. For text, numbers and dates this looks not so bad, but what when I want to reference an entity in another table? For instance, when I select an employee with id 1 in the form, the value column will contain the string "1". This is pretty simple, but this means I lost my relation reference to the employee table.

    So how would others solve this problem? Is my choice the best design, or are there other (and better) ways to achieve the same?

    Thanks in advance Wink
    Wednesday, March 26, 2008 8:41 PM

Answers

  • I would store the user-defined fields in XML format and store that XML string in a field in your table.  This way you could even dynamically build an XSD if you wish and store that for validation as well.

     

    An XML field could be used if the DB is SQL 2005 or later so you query right into the XML field.

    Thursday, March 27, 2008 6:32 PM

All replies

  • I'm not clear what you mean by field, I assume you mean the entity in the designer rather than the control the user will evenutally see?
    Thursday, March 27, 2008 6:44 AM
  • To maintain data integrity the values could be stored in a table like this

     

    RequestId PK

    FormId PK

    FieldId PK

    FieldType (date, text, employee, customer, etc)

    DateValue

    TextValue

    EmployeeId FK

    CustomerId FK

    etc.

    Thursday, March 27, 2008 11:54 AM
  • yes, of course that is an option Smile But what would you prefer? My version where everything is stored as a string, or this version, where you might have a bunch of columns of which only one will be used for each row? And you must keep in mind that new types can be added at a regular interval Smile
    Thursday, March 27, 2008 3:52 PM
  • I would store the user-defined fields in XML format and store that XML string in a field in your table.  This way you could even dynamically build an XSD if you wish and store that for validation as well.

     

    An XML field could be used if the DB is SQL 2005 or later so you query right into the XML field.

    Thursday, March 27, 2008 6:32 PM
  • If you're talking about storing the form layout, then yes for xml, but I'd add that you should consider a XAML schema.
    Friday, March 28, 2008 6:55 AM
  • If all the data is stored as string you will miss the ability to sort and filter the data in the database. Dates and numbers can be filtered and sorted and foreign keys can be joined with other tables to be sorted and filtered with values from other tables. If foreign keys are stored with out referential integrity the code can no longer trust the data.  So at the end the complexity is just moved to another place and can possibly grow more than expected. 

    Friday, March 28, 2008 8:46 AM
  • We have a very similar application then you seem to have. We store all user defined values in a table with the following columns:

     

    Guid (Primary Key)

    Entity (Foreign key to the table this value belongs to)

    name (name of the user defined field)

    datatype (this defines what type the value is)

    integervalue

    decimalvalue

    stringvalue

    datetimevalue

     

    Enums are stored in the integervalue column but do have a separate datatype (so we can distinguish them).

    Booleans are stored in the integervalue as well but have a separate datatype as well.

    The reason for this separate datatype for us is, to show an appropriate control in the UI. For a boolean an tickbox as an example.

     

    For us this works just fine.

     

    Tuesday, April 1, 2008 6:53 PM
  • I'd recommend NOT going the row per value route. In this day an age it is pretty simply to create actual physical tables based on your meta data. Row per field/value/column (whatever you call it) is fine for low loads but becomes a self-joining nightmare after a while.

     

    Wednesday, April 2, 2008 7:06 AM
  • Touching the database may be simple for developers but not for users. Even for developers in a dev, test, prod environment touching the database takes time.

     

    If the requirement is that the end users must be able to add new fields then in my opinion the row per value route is an acceptable way to do it. But is it the best way to do it? Is there another way to do it with out hard coding and compiling a new version of the application?

     

    Wednesday, April 2, 2008 12:49 PM
  •  

    It is easy for users cause your code should create the physical tables for them. Think of your code as a friendly version of SQL Workbench\Enterprise Manager. Row per column is bad news, been there bought the t-shirt, etc, etc. If you want a clue just think about what is in SQL Server itself. Take a look at syscolumns and see how it has meta data for the columns and yet creates physical tables. It's really not that difficult to do, and the majority of the ORMs out there will thrive off the physical representation so the rest of your code could be easier to write too.

     

     

     

     

    Wednesday, April 2, 2008 12:55 PM
  • I am not convinced that LINQ would work well with dynamically created fields in the database. The code has to be recompiled with changes in the physical representation. If the database changes the data classes have to be updated.

    Wednesday, April 2, 2008 1:39 PM
  •  

    True you do have to compile some aspect of your DAL when the schema changes, but again this isn't difficult. If it were COM then I wouldn't (and didn't) go that route but with .net compiling code is pretty easy...not withstanding some issues I've had getting debug info from them.

     

    Wednesday, April 2, 2008 1:45 PM
  •  pkr2000 wrote:

    True you do have to compile some aspect of your DAL when the schema changes,

     

    To compile the application is no option in our case. The user has to be able to define additional fields at runtime and don't want to restart the application. Of course there are ways around that as well, but they are certainly more difficult then the row per column approch.

    As I said, it works perfectly for us. Since more then two years now. I would design it exacly the same way again. It has not become a nightmare . The advantage is, that I also can store some metadate for every custom field and that I have separated the custom fields from the application fields completely.

    The disadvante is the querying with sql becomes harder but this is not a major point for us.

    The performance is actually better then you might think. At least that I have thought before I have tried the solution.

     

    regards

    Paul

    Wednesday, April 2, 2008 8:38 PM
  • Horses for courses whichever design fits the purpose better.

    Wednesday, April 2, 2008 9:37 PM