Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm doing a feasibility study for storing data in the following structure with SQL SERVER 2008:

-MYTABLE |ID|RECORDS|BRANCH_OFFICE|MONTH|YEAR

The RECORDS column has the XML data type and it will look like:

<visits>
  <visit id="000112233">
    <costumer>Mr. One Costumer</costumer>
    <date>2011-02-10</date>
    <employee>MAT01234</employee>
  </visit>
  <visit id="000112234">
    <costumer>Mr. Another Costumer</costumer>
    <date>2011-02-12</date>
    <employee>MAT01235</employee>
  </visit>
  <visit id="000112235">
    <costumer>Mr. Some Costumer</costumer>
    <date>2011-02-12</date>
    <employee>MAT01234</employee>
  </visit>
  <visit id="000112236">
    <costumer>Mr. Some Costumer</costumer>
    <date>2011-02-15</date>
    <employee>MAT01235</employee>
  </visit>
</visits>

I'm intending to query the xml column with xquery, is this a good way to store these data? If i want to get the list of visits occurring in 2011-10, adding a tag with the difference of days between the "date" tag and now, how could it be?

share|improve this question
1  
Use XML if your data spec is variable or loosely defined. That's the primary benefit of using XML in SQL Server. If it's a fixed schema, however, you are better off taking Mark's suggestion below and mapping your XML schema to tables. –  Nick Chammas Oct 20 '11 at 14:38
    
also posted on SO –  Jack Douglas Oct 20 '11 at 15:05
    
@Julio can you post your SO comment here as well please? –  Jack Douglas Oct 20 '11 at 15:07

1 Answer 1

...is this a good way to store these data?

No, this is probably the worst way to store this data if you want to query it effectively. You need to study some database normalisation examples before you go any further with this

This is a very quick sketch up of a possible schema that could be used. Incomplete and untested but it should be clear how this maps to your XML fragment.

CREATE TABLE dbo.Customer
(
    CustomerId INT NOT NULL
    , Title VARCHAR(20)
    , FirstName VARCHAR(100)
    , LastName VARCHAR(100)
)

ALTER TABLE dbo.Customer
ADD CONSTRAINT PK_Customer PRIMARY KEY (CustomerId ASC)

CREATE TABLE dbo.Employee
(
    EmployeeNumber VARCHAR(10) NOT NULL
    , Title VARCHAR(20)
    , FirstName VARCHAR(100)
    , LastName VARCHAR(100)
)

ALTER TABLE dbo.Employee
ADD CONSTRAINT PK_Employee PRIMARY KEY (EmployeeNumber ASC)

CREATE TABLE dbo.Visit
(
    VisitId INT NOT NULL
    , VisitDate DATETIME
    , CustomerId INT
    , EmployeeNumber VARCHAR(10)
)

ALTER TABLE dbo.Visit
ADD CONSTRAINT PK_Visit PRIMARY KEY (VisitId ASC)

ALTER TABLE dbo.Visit WITH CHECK ADD CONSTRAINT [FK_Visit_Customer] FOREIGN KEY(CustomerId)
REFERENCES dbo.Customer (CustomerId)

ALTER TABLE dbo.Visit WITH CHECK ADD CONSTRAINT [FK_Visit_Employee] FOREIGN KEY(EmployeeNumber)
REFERENCES dbo.Employee (EmployeeNumber)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.