Geek Answers Handbook

How to set up a data model for a custom application

I have an ASP.NET data entry application that is used by several clients. The application consists of several data entry modules that are common to all customers.

Now I have several clients who want to add their own custom module, which usually consists of a dozen or so data points. Some values ​​will be textual, others - numerical, some - drop-down lists, etc.

I need suggestions for processing a data model for this. I have two thoughts on how to handle. First, it would create a new table for each new module for each client. It's pretty clean, but I don't particularly like it. My other thought is to have one table with columns for each user data point for each client. This table will have many columns and many NULL values. I don’t really like any solution, and I suspect there is a better way to do this, so any feedback you have will be appreciated.

I am using SQL Server 2008.

+4
source share
2 answers

As always with these questions, "it depends."

A table of terrible keys.

This approach is based on a table listing the fields and their values ​​as separate records.

CustomFields(clientId int, fieldName sysname, fieldValue varbinary)

Benefits:

  • Infinitely flexible
  • Easy to implement
  • Easy to index
  • nonexistent values ​​do not take up space

Inconvenience:

  • Displaying a list of all records with a complete list of fields is a very dirty query

Microsoft path

Microsoft's way of doing this is sparse columns (introduced in SQL 2008)

Benefits:

  • Blessed by people designing SQL Server
  • records can be requested without the need for mosaic vaults
  • Fields without data do not take up disk space

Disadvantage:

Xml tax

You can add the xml field to the table, which will be used to store all the "additional" fields.

Benefits:

  • unlimited flexibility
  • can be indexed
  • efficient storage (when it fits on the page)
  • With some xpath gymnastics, fields can be included in a flat record set.
  • can be implemented circuit with collections of circuits

Disadvantages:

  • it is not clear that in the field
  • SQL Server xquery support has gaps that sometimes cause your data to become a real nightmare.

There may be more solutions, but for me these are the main rivals. Which one to choose:

  • The key value seems appropriate when the number of additional fields is limited. (say no more than 10-20 or so)
  • Sparse columns are more suitable for data with many properties that are infrequently populated. It sounds more appropriate when you can have many extra fields. Column
  • xml is very flexible, but a pain in the request. Suitable for solutions that are rarely written and rarely requested. i.e.: do not start aggregates, etc. to the data stored in this field.
+2
source

I suggest you move on to the first option that you described. I would not think about that. The second option that you indicated would be a bad idea, in my opinion.

If there are fields common to all modules that you add to the system, you should consider them in one table and then have other tables with fields related to a specific module associated with the primary key in the shared table.This is basically table inheritance ( h ttp: //www.sqlteam.com/article/implementing-table-inheritance-in-sql-server ) and will centralize the general data of the module and facilitate the request between modules.

0
source

More articles:


All Articles