x

[Friday-quiz] What data type would you choose?

Ok, so it's Friday afternoon - and I don't know about your offices, but it's showing badly here. I thought I'd do a bit of database design investigation for my current client (even though I'm on a .NET contract). Anyway, some quiz questions:

You have to configure several hundred devices with a statically laid out configuration, which has defined elements. Do you:

  • Create a schema which maps to the relationships within the static configuration and populate the devices from there?
  • Create an entity-property style table so that you can extend the configuration easily if you need to while retaining the ability to search relatively easily?
  • Stick it in a massive xml blob

Given that you've chosen to stick it in a massive xml blob do you:

  • Put in an XML Namespace, qualifying the schema elements of the xml and then create an xml column that conforms to that namespace?
  • Put it in an untyped xml column and index it?
  • Use ntext

Given that you've chosen to use ntext, do you:

  • Answer user queries using a view that you've created to pull out the common elements of the xml?
  • Answer user queries by searching the table for XML fragments using patterns or regular expressions?
  • Tell users that it's too hard to query the table, so it's not really possible to tell what is configured where?

Head. Wall. Smash. Blood.

Any of you had similar experiences?
more ▼

asked Oct 08, 2010 at 05:29 AM in Default

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Should this be a wiki?
Oct 08, 2010 at 05:40 AM ThomasRushton ♦
I don't know - i've wikified it, but personally I don't really like the whole wiki thing, never really gelled with it.
Oct 08, 2010 at 05:50 AM Matt Whitfield ♦♦
Nah, this is educational.
Oct 08, 2010 at 06:13 AM Grant Fritchey ♦♦
Ok cancelled it again - unless i've got the wrong end of the stick?! It is Friday afternoon - oh so badly!
Oct 08, 2010 at 06:23 AM Matt Whitfield ♦♦
Yep. This is often because the business has to have it ASAP but they just don't know what 'it' is. I sit with the development team and overhear stuff that makes my skin crawl. I'm not the most assertive guy in the world but I've had to learn to get straight into the discussion at that stage where change is still cheap.
Oct 08, 2010 at 08:35 AM David Wimbush
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

BWA-HA-HA-HA

It's not Friday afternoon here, yet, but you just put me in the mood.

Yes, lots of similar experiences. My favoritee is the multi-statement table valued user defined functions system. The developers saw how UDFs worked, so they started putting them into the design. They had UDFs. Those UDFs called other UDFs and joined them together to create new structures. These were used with other UDFs combined with calls to sitll other UDFs that made structures that could be consumed by other UDFs. Basically, they built an object oriented data structure using UDFs.

First time I saw it, I said, this won't work. I was dismissed out of hand because, it had been tested. A lot. In dev. With one row per table and one user. So, it was released to production, where there were tens of thousands of rows and hundreds of users. The rest, as they say, is history.
more ▼

answered Oct 08, 2010 at 06:12 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

Awesome - I'd love to have seen that one!
Oct 08, 2010 at 06:17 AM Matt Whitfield ♦♦
sounds awesome. How long did it take to fix, or did they just add hardware?
Oct 08, 2010 at 06:20 AM Fatherjack ♦♦
Did you save any of the execution plans?
Oct 08, 2010 at 06:21 AM Blackhawk-17
@Fatherjack the first fix, I wasn't consulted on, resulted in an 86 table join (they just replaced the UDFs with views and went on their merry way). It took three tries and three different consultants (becuase they wouldn't let me do it, although the third consultant was EXTREMELY smart and I learned a lot from him).
Oct 08, 2010 at 06:28 AM Grant Fritchey ♦♦
+1 for grant as well. My question is, has anybody NOT experienced something like this?
Oct 08, 2010 at 07:26 AM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left
--- Yes .
more ▼

answered Oct 08, 2010 at 05:48 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

@Fatherjack - Bottom line... you answered the question as posed. +1
Oct 08, 2010 at 06:20 AM Blackhawk-17
yeah, sometimes I am a little too literal for my own good. Was about to delete this as Grant has validated the question as "educational" when I was thinking it was a bit frivolous.
Oct 08, 2010 at 06:24 AM Fatherjack ♦♦
Me? Ask a question that was a bit frivolous?? What??? >:)
Oct 08, 2010 at 06:29 AM Matt Whitfield ♦♦
I have to agree, answered the question.
Oct 08, 2010 at 07:25 AM CirqueDeSQLeil
+1, sorry for delay - just got some more votes. Hella-way lame.
Oct 08, 2010 at 07:31 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

Depressingly familiar. I spent some time recently with a database that held call centre-type data (details of caller, type of query, etc) in an XML string. Stored in a SQL Server text field.

Previous DBA said "No, we can't report on this". I took that as a challenge... Didn't take long to get a rudimentary report up & running.

The real problem was that there were about 300 different "call types", each with their own little foibles once you got beyond the basic caller / handler information.
more ▼

answered Oct 08, 2010 at 05:43 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

You have to love stuff like this. I don't know how it's got to 2010 with this stuff still popping up. Is it that the percentage of people who really don't understand computing is the same - except that now there are more people - so the volume of poor code / architecture is actually going up?
Oct 08, 2010 at 06:04 AM Matt Whitfield ♦♦
+1, sorry for delay - just got some more votes. Hella-way lame.
Oct 08, 2010 at 07:31 AM Matt Whitfield ♦♦
Just wondering if there's a way for users of more than a certain reputation to have more votes per day... Or if it's something that should be considered by the devs.
Oct 08, 2010 at 09:53 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
I know the right answer to this. You write out the nText column as a series of files and query them via xPath with XMLStarlet using its 'sel' command
more ▼

answered Oct 08, 2010 at 10:00 AM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

Almost perfect. Are you sure you couldn't squeeze an Access database and a couple of spreadsheets in there somewhere though?
Oct 08, 2010 at 10:12 AM Grant Fritchey ♦♦
Yeah, I mean come on Phil - surely you can get a type-writer and punch-cards in there?!? :)
Oct 08, 2010 at 11:26 AM Matt Whitfield ♦♦
I've got this strange urge to actually try it out.
Oct 08, 2010 at 11:56 AM Phil Factor
Don't forget to put the MDB file on a network share!
Oct 08, 2010 at 12:13 PM David Wimbush
while you're at it, write the files to a source code repository then write a front end to extract the version the user wants before xQuerying
Oct 08, 2010 at 04:32 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x150
x7
x7

asked: Oct 08, 2010 at 05:29 AM

Seen: 1909 times

Last Updated: Oct 08, 2010 at 05:30 AM