1

I have an object whose fields I would like to store in a database. I will be using SQL Server Compact Edition (with Visual C# Express 2010). For the record, I'm fairly new to programming with databases (and databases in general). This program will be used every day to read emails, process the orders inside them, store them, and access them when necessary to help with completing the orders. The list of orders is going to become much to large to store in a List, write to a file, create the List from a file, etc. The problem is that each order contains a list of the items purchased. I am aware that I can serialize the list in binary or XML, and use that data as the field. However, this prevents me from searching/selecting based on that list. For instance, if I wanted to find an order based on what items are in it, or see how many times a particular item has been purchased. Since the list will be of arbitrary size, I can't just create a bunch of fields and fill only the ones I need (which in my opinion, is a bad idea anyway).

While writing this I realized a mistake. If I serialize the list again, I could compare the serialized data to find the same list again (though, this assumes that the same data is serialized the same way each time). However, I'm still prevented from finding any particular item.

So, is there any way to store the list of items, in a fixed number of fields (preferably 1) and still be able to search its contents with a query (I will most likely be using LINQ)?

Edit: To address what I've gotten so far: first, thanks! I'm starting to piece together what I have to do, but I'm not quite there. The consensus seems to be to have a table for each set of items. Does that mean I'd be creating thousands of tables each month?

After re-reading my question I realize I have to be more clear. As the order comes in, I parse the data and store it in an Order object, which consists of the customer's information, and the list of items. Here is a simplified version of what I'm trying to store:

class Order{
    private DateTime date;
    private String orderNumber;
    private String buyerName;
    private List<Item> items;
    private String address;
}

class Item{
    private String itemCode;
    private String description;
    private int quantity;
}

So would I have to create a new table for each List I create, or am I missing something?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Fractalife
  • 87
  • 1
  • 1
  • 7
  • 1
    Please post the code you have written thus far. Some idiot broke my crystal ball :( – Brian Apr 03 '13 at 17:03
  • Is there a compelling reason you're not building a related one-to-many table for the items? – Mike Perrenoud Apr 03 '13 at 17:04
  • @MichaelPerrenoud Yes, a great one! I don't know what that is. – Fractalife Apr 03 '13 at 17:27
  • You would only need to create one LineItem table. Each record in the table would reference one order. Then, when you have the orderid, you can get all of the line items associated with that order. – wilsjd Apr 03 '13 at 17:49

3 Answers3

5

Update: This may be a helpful reference for One-to-many relationships if you are new to the subject (especially check out the second and most upvoted answer): Create a one to many relationship using SQL Server

You'll want to create a new table in the database for your line items then create a foreign key relationship to the order table. It will be a one to many relationship. Some thing like the following - obviously you'll need to create a FK relationship, but you get the gist.

Existing

CREATE TABLE Order (
  OrderID INT,
  PONumber VARCHAR,
  ItemsList VARCHAR
)

New

CREATE TABLE Order (
  OrderID INT,
  PONumber VARCHAR
)

CREATE TABLE LineItem(
  LineItemID INT,
  Description VARCHAR,
  Quantity INT,
  SequenceNumber INT,
  OrderID INT -- <--- Important one here
)

Note that if you want to create just a simple lookup, the relationship would go the other way.

Community
  • 1
  • 1
wilsjd
  • 2,178
  • 2
  • 23
  • 37
  • I'm pretty sure you've got that FK reference backwards. You have an `Order` with many `Items` therefore the `Items` table would contain an `OrderID` for example. Here, you've got an FK to what would be something like a lookup table. – Mike Perrenoud Apr 03 '13 at 17:22
  • I think I see what you mean, the wording is awkward. I'll update accordingly. – wilsjd Apr 03 '13 at 17:23
  • Thanks, it took me a minute but I understand what you're saying. I write all the items to their own record, and when I want to put the order back in to the object I just select the items with the corresponding order ID. Still don't know what FK is but I guess I'll figure it out. Edit: Foreign Key. Which would be the OrderID. – Fractalife Apr 03 '13 at 19:06
  • But what if you wanted to go from an Order to each of its LineItems? That's what I think of when I hear `one-to-many`, although clearly that's incorrect thinking. – Sinjai Jun 29 '17 at 20:24
  • One order to many line items is correct. You would query the line items table where orderid = your order id. – wilsjd Jun 29 '17 at 20:29
0

Instead of storing the list as a field, you can create a separate table that hold it's items. And records in this ListTable will have a field pointing to record ID in your original table. That way you can write various queries afterwards.

alex
  • 12,464
  • 3
  • 46
  • 67
0

This can be solved in several ways, but if you want to be able to query the data directly, you should redesign your database with a seperate table where you store your list data with a reference ID. After all, databases are all about data lists.

jugg1es
  • 1,560
  • 18
  • 33