0

I am looking at a cloud based solution which will give people the ability to enter information which is stored in a SQL database.

The benefits of my application will be that people can also change what type of information is stored (i.e an administrator would be able to add/remove certain attributes to change what data people can store).

Doing this in a relational database does work but it means the administrator would be changing the actual structure of the database which has so many risks and issues and I really don't want to go down this route.

I have thought about using XML, so one table contains two tables for example:

Template Data columns (ID, XML) - This will contain the "Default Templates/Structure" of what people will enter which will is used when the users enter data and submit

Data Table columns (ID, XML) - This will contain the actual data using the XML template of my first column but store the actual data in it

Does this sound like it would work and could I hit potential performance issues? A lot of the data will be searchable and could potentially have a LOT of records in the database. - I guess I could look at storing the searchable data in separate fields that the administrator can't modify.

Thanks

realtek
  • 831
  • 4
  • 16
  • 36
  • If you design your relational database properly, all the administrator would have to do is write data. – Dan Bracuk Feb 11 '14 at 13:10
  • Yes but the benefits of the application is that they can 'change' what data is stored. So for example, there maybe a table which contains Customers, they may want to also capture something that does not exist in the database. So they would need to add an attribute for it. The administrator needs to be able to change the structue of this – realtek Feb 11 '14 at 13:18
  • Sometimes you are stuck with something like this - been there too. There is not a lot you can "design properly" when it is a property bad or document store and you have limited control over the data going in. There are edge cases. I prefer nice table structures, but there are cases the XML Data type makes sense. – TomTom Feb 11 '14 at 14:35

2 Answers2

1

It is possible and if you do it a little smart it is feasible.

Contrary to Justings wroong answer you are not stuck with string manipulation and search.... if you actuall care to read the documentation.

SQL Server added a XML field type a long time ago.

This takes XML (only) and decomposes it internally and has an indexing mechanism (cech http://technet.microsoft.com/en-us/library/ms191497.aspx for details).

Queries then look like:

SELECT 
    EventID, EventTime,
    AnnouncementValue = t1.EventXML.value('(/Event/Announcement/Value)[1]', 'decimal(10,2)'),
    AnnouncementDate = t1.EventXML.value('(/Event/Announcement/Date)[1]', 'date')
FROM
    dbo.T1
WHERE
    t1.EventXML.exist('/Event/Indicator/Name[text() = "GDP"]') = 1

(copied from How to query xml column in tsql)

How far it gets you depends - this is heavier on the database and may have limitations, but it is a far cry from the alternative of storing strings and saying good bye to any indexing.

You can actually even add xml schemata so the data has to conform to some specific schema.

Community
  • 1
  • 1
TomTom
  • 61,059
  • 10
  • 88
  • 148
  • Excellent answer TomTom, I will read up on that and look into the documentation and example. Thanks a million – realtek Feb 11 '14 at 15:30
  • @realtek Be aware that those thignsa re intensive on the server. Try to avoid it - no replacement for "real tables". But if you need to store "arbitrary data" or "documents", man, they rock ;) And as you say in your case.... sometimes it is needed. – TomTom Feb 11 '14 at 15:39
-1

This is possible but data retrieval will suffer if you will query data based on the values in the XML string. If you will use this you're stuck with using a LIKE filter which is not recommended for searching a table with too many rows. If you will always read data using the ID column only I think this would be great.

On the other hand, if you will separate the data in the XML to several columns, you can refine the way you query data based on multiple columns. This will speed up your searches most especially if the columns are indexed.

Justin Paul Paño
  • 936
  • 5
  • 7
  • Yes I agree, I suppose I could do a hybrid solution and have one Column such as "CustomAttributes" and then use this for the attributes that will be added by the administrator. This should be fairly small amount which shouldn't impact search too much? – realtek Feb 11 '14 at 13:25
  • WRONG - totally in fact. See, SQL Server has a XML field type that then internally is deconstructed into the individual parts and can have a proper index and does uspport quite efficient querying. So, you are NOT stuck with using LIKE. There is a nice query language extension in tsql - check http://stackoverflow.com/questions/13195922/how-to-query-xml-column-in-tsql for an example. – TomTom Feb 11 '14 at 14:31
  • OK my bad. It's up to you @realtek. But this is best if you are performing most of the business logic in your stored procedures instead of your programming language. I don't know but I think this type of setup is costly to the server and more harder to implement. – Justin Paul Paño Feb 11 '14 at 14:49
  • @JustinPaulPaño I fully agree - it is resource intensive. Just it is a lot better than strings. I try to avoid it, but I do that for example for configuration files for stuff pulling config info from the database. Nice little XML according to a schema, witha free payload that the program can fill as it wants. – TomTom Feb 11 '14 at 15:31