Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
petteroe
Starting Member
2 Posts |
 Posted - 2006-11-14 : 01:09:19
|
Hi,I am redesigning the DB structure for an e-Wallet application. Basically I am pulling different functionalities into one core, which will then be basically just a long list of money transactions. These transactions can be of different types. Since I don't have any experience with using the new SQL server 2005 XML column type, I need to know which would be the best solution: - Create a single table for transactions with the basic columns (ID, amount, date, transaction type etc) plus an Xml column that holds the data that is specific for a certain type of transaction
- Create a base transaction table containing the basic columns as above, but have satellite tables containing the data that is specific for certain types of transactions
All feedback on this is appreciated. Thank you.Morten |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-11-14 : 07:54:22
|
| As a purist, I'm inclined to suggest you stay away from the XML column. As a realist, I'd say it depends on what you want to do with the data specific to certain transaction types. If you are going to be querying that data often or have business rules applied to that data, I think having it in XML would be a pain. I would only entertain the XML solution if you plan on hardly ever using that data for anything other than spitting it back out to some consuming application in XML form.Jay White |
 |
|
|
petteroe
Starting Member
2 Posts |
Posted - 2006-11-14 : 11:07:21
|
| Hi Page47,I will be doing no business logic in the database. Just data integrity checks. I can do this by only using the basic columns. So the Xml column will only have the derived transaction data type specific information in it. It might be queried as part of a support staff tool, but those queries will be rare. My biggest issue is probably whether or not having an Xml column will slow down queries, or if there are other issues with using an Xml column that I haven't thought of.One more question, the most used query against this table would be getting the balance on an account by using Sum(Amount). So I need to know; if I create an index on the account id column and include the amount column as a non-indexed column in the index, am I right in assuming that the physical table will not be hit at all for the balance query? If this is the case, I am a lot less worried about using the Xml column.BTW: Is there some kind of rule-of-thumb that says how slow a query against an Xml column is compared to against a regular table? Like "Xml queries cost 5 times more as a rule of thumb..."Thanks,Morten |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-11-14 : 11:11:29
|
don't use xml column for this.Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
|
|
|
|
Xml column or "standard" db layout