Database schema for a restful service

Question

I am writing tables in database for a restful service that exposes a resource called Orders.

Orders(id, items, user, total_amount)

I am using XML for the same.My question is how do I store "items" in my Orders table per OrderId and return the list in XML ? How do we handle multiple items here ?

What I mean is,if I want an XML response like this, how do I store the data in database?

<Order>
<Id>1</Id>
<Item>A</Item>
<Item>B</Item>
<Item>C</Item>
<User>Simran</User>
<Total_amount>1100</Total_amount>
</Order>

Database Tables I have so far:

create table Users
(
Id int not null primary Key identity(1,1) constraint fk_UserId_Id references Orders(UserId),
name nvarchar(50),
phone nvarchar(10)
);

create table Items
(
Id int not null primary Key identity(1,1) constraint fk_item_Id references Orders(ItemId),
name nvarchar(50),
Price int
);

create table Orders
(
id int not null primary Key identity(1,1),
ItemId int,
UserId int,
);

You need more tables. http://stackoverflow.com/questions/20330565/database-design-items-and-orders-tables — Mark Leiber, May 18 '15 at 15:04

score 2 · Accepted Answer · answered May 18 '15 at 15:16

2

This all depends on how you would like to store your data.

Denormalizing your data would probably create 3 tables from your example, A table for Orders, Users and Items.

In terms of producing XML from the DB query results you would need to extract the data into your XML Model. I would recommend separating this logic and producing a transformation tier, this way you are not messing with your data service layer when manipulating your objects.

Your multiple items in the XML would also have to be wrapped by an outer element, like so:

<Orders>
  <Order>
   <Id>1</Id>
   <Item>A</Item>
   <Item>B</Item>
   <Item>C</Item>
   <User>Simran</User>
   <Total_amount>1100</Total_amount>
  </Order>
  <Order>
   <Id>2</Id>
   <Item>A</Item>
   <Item>B</Item>
   <Item>D</Item>
   <User>Simran</User>
   <Total_amount>1300</Total_amount>
  </Order>
</Orders>

answered May 18 '15 at 15:16

robinsio

93
6

That's exactly what I want. I have those 3 tables in the database but I am pretty new to this XML thing. Could you please refer me to such links where I can get some idea on extraction of the data in this XML structure? – simar kaur May 18 '15 at 15:20
1

@simar kaur: The outline is in the XML structure. 1) Select all orders for a user. 2) Create Order XML. 3) For each order, select all items. 4) For each Item, create Item XML. – Gilbert Le Blanc May 18 '15 at 15:26
What language are you writing your restful service in? Implementations of XML deserialization and unmarshalling vary but are easy to find with a quick search. – robinsio May 19 '15 at 07:51

Database schema for a restful service

1 Answers1