How Best to Store this Data

[George Durzi]

I have a company form that I need to web-enable. The form has about 50 data
items with some of these data items able to have N sub data items. I'm
considering ways to store this form in my database.

If I were to go for SQL tables, I would end up with several tables, the main
one having many columns. Maintaining the stored procedures and methods
involved in this would be a pain.

What are some other good techniques for storing something like this?

I was considering coming up with an XSD that defines the data stored in this
document, and then store the whole document as Xml. Does this idea have any
glaring disadvantages?

Just looking for some good ways to tackle this.

Thank You

[Bryan]

George
There are always advantages and disadvantages to everything... if you're going to have a lot of records... you'll have to deal with the pain of keeping up stored procedures and keeping evrything in SQL. If the amount of records are going to be small then I'd go xml (This is what I've been hearing). Searches through xml start to get slower as you create bigger amounts of data. SQL is considerably faster through searches as your data is bigger.

[George Durzi]

One one hand, the SQL way is familiar, but as you said, it becomes a pain to
maintain the stored procedures and methods, etc.

The Xml alternative would be an interesting and different way to do this (
which is more preferable than sitting down to write a 50 parameter stored
proc ;)

I'll probably have about 1000 records per year. Does that qualify as a lot
of records, or is it still a manageable quantity for doing searches, etc.?

Thank you Bryan

"Bryan" <an*******@discussions.microsoft.com> wrote in message
news:7F**********************************@microsof t.com...

George,
There are always advantages and disadvantages to everything... if

you're going to have a lot of records... you'll have to deal with the pain
of keeping up stored procedures and keeping evrything in SQL. If the amount
of records are going to be small then I'd go xml (This is what I've been
hearing). Searches through xml start to get slower as you create bigger
amounts of data. SQL is considerably faster through searches as your data
is bigger.

[Bryan]

George..
Take a look at a product called RapTier... it writes your insert, update commands and stored procedure for you. I use it cause I just can't stand writing all the junk code for VB and C# for stored procedures. It keeps a lot of your hair on your head when dealing with changes to the Tables. It's free for coontrolling under 15 tables. Over this amount you'll have to pay. If, like me, you can't deal with kind of stuff... you'll like this product

I'd say a 1000 records are starting to get big for xml... especially with 50 columns. I had a teacher in my XML clss that when XML first came out they through a lot of records into it. They found that when they were doing searches.... they definitely didn't want what XML was giving them... latency times on searches. XML is great for webservices and transfering info between users but keeping records for a db in a stationary environment??? I wouldn't.

[Grant]

You're going to have to maintain something at some point... either sprocs or
your own code.

It sounds like you really do have a relational database (at least
conceptually) - even though you apparently don't want to store it physically
in a database product like SQL Server. If that's true (your data really is
relational), then depending on how you use the data, it might become much
more of a maintentance problem to work with your relational data in XML vs
in a true relational database.

If you are simply not wanting to take the time to do it using a tool
designed to work with relational data (SQL Server, Access, etc), then you'll
have to take the time to roll your own code that will do what the true
database would have made easy (e.g., a SQL statement executed against a
table is much simpler to write and maintain than your own code that loops
through flat data...).

Of course your not telling us much about how you plan to use the data and
the nature of the data limits our ability to offer you much specific
guidance.

Good luck

-G

"George Durzi" <gd****@hotmail.com> wrote in message
news:eQ*************@tk2msftngp13.phx.gbl...

One one hand, the SQL way is familiar, but as you said, it becomes a pain to maintain the stored procedures and methods, etc.

The Xml alternative would be an interesting and different way to do this (
which is more preferable than sitting down to write a 50 parameter stored
proc ;)

I'll probably have about 1000 records per year. Does that qualify as a lot
of records, or is it still a manageable quantity for doing searches, etc.?

Thank you Bryan

"Bryan" <an*******@discussions.microsoft.com> wrote in message
news:7F**********************************@microsof t.com...

George,
There are always advantages and disadvantages to everything... if you're going to have a lot of records... you'll have to deal with the pain
of keeping up stored procedures and keeping evrything in SQL. If the

amount of records are going to be small then I'd go xml (This is what I've been
hearing). Searches through xml start to get slower as you create bigger
amounts of data. SQL is considerably faster through searches as your data
is bigger.

[George Durzi]

You both are right. I think the best way is to suck it up and do it in the
good old SQL way. Bryan, thanks for the product suggestion for creating all
that junk code... that will definitely help.

"Grant" <GM**@spamthis.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

You're going to have to maintain something at some point... either sprocs or your own code.

It sounds like you really do have a relational database (at least
conceptually) - even though you apparently don't want to store it physically in a database product like SQL Server. If that's true (your data really is
relational), then depending on how you use the data, it might become much
more of a maintentance problem to work with your relational data in XML vs
in a true relational database.

If you are simply not wanting to take the time to do it using a tool
designed to work with relational data (SQL Server, Access, etc), then you'll have to take the time to roll your own code that will do what the true
database would have made easy (e.g., a SQL statement executed against a
table is much simpler to write and maintain than your own code that loops
through flat data...).

Of course your not telling us much about how you plan to use the data and
the nature of the data limits our ability to offer you much specific
guidance.

Good luck

-G

"George Durzi" <gd****@hotmail.com> wrote in message
news:eQ*************@tk2msftngp13.phx.gbl...

One one hand, the SQL way is familiar, but as you said, it becomes a pain

to

maintain the stored procedures and methods, etc.

The Xml alternative would be an interesting and different way to do this

( which is more preferable than sitting down to write a 50 parameter stored proc ;)

I'll probably have about 1000 records per year. Does that qualify as a lot of records, or is it still a manageable quantity for doing searches, etc.?
Thank you Bryan

"Bryan" <an*******@discussions.microsoft.com> wrote in message
news:7F**********************************@microsof t.com...

George,
There are always advantages and disadvantages to everything... if

you're going to have a lot of records... you'll have to deal with the pain of keeping up stored procedures and keeping evrything in SQL. If the

amount

of records are going to be small then I'd go xml (This is what I've been
hearing). Searches through xml start to get slower as you create bigger
amounts of data. SQL is considerably faster through searches as your data is bigger.