Database list of objects as property of class

Question

I have a class box which contains few properties, one is a counter for an id that increments every time a new object is initialized and the other property is a List<Item> that contains many object of type Item, that is another class with its own properties (name, weight etc..). I'm trying to build a database for this application, but I really don't know how to represent my List<Item> property in my database. I'm a beginner, especially in databases within c#, so please be as clear as possible.

EDIT

Item.cs

class Item
{
    private string name;
    private int price;
    private int weight;

    public Item() { }

    public string Name
    {
        get { return name; }
        set { name = value; }
    }

    public int Weight
    {
        get { return weight; }
        set { weight = value; }
    }

    public int Price
    {
        get { return price; }
        set { price = value; }
    }
}

What is your definition for a "database" ? For example you could serialize an instance of your object as a JSON and save that JSON to a postgreSQL (SQL) or MongoDb (NoSQL) database. — Cetin Basoz
– Cetin Basoz, Commented Nov 20, 2015 at 16:06
Replies are assuming that you are talking about an RDBMS. You didn't tell about that detail, and also even with RDBMS your issue could be modeled with JSON or XML datatype too. It looks like you are after something like a "Cart" and "Items" added to it, which is modeled differently based on the specific "database" you want to persist it. — Cetin Basoz
– Cetin Basoz, Commented Nov 20, 2015 at 16:14

David · Accepted Answer · 2015-11-20 16:16:13Z

2

If a Box has a collection of Item objects, then your database would have two tables for this:

Boxes
----------
ID
Name
etc.

Items
----------
ID
BoxID
Name
etc.

Things like Name and etc. would be the properties which describe any given instance of that type. The ID values are the unique identifiers for the items. The main thing to notice here is the BoxID column in Items. For any given Item there is a reference to the Box which contains it.

So when you save a Box object to the database, first you would save the object itself and then, given its ID, save all of the Item objects.

This is called a "one to many" relationship. "One" Box has "many" (zero or more, in this case) Items.

Edit: Based on your comment below, do you mean that this would be a many-to-many relationship? If that's the case then you'd need a table to hold the relationship itself. Something like this:

Boxes
----------
ID
Name
etc.

Items
----------
ID
Name
etc.

BoxItems
----------
BoxID
ItemID

Any Box can relate to many Items and any Item can relate to many Boxes. Consider that the relationship itself is an "entity" in the model.

To help visualize this, a very common example is Movies and Actors. One can have a collection of Movies and a collection of Actors, and they clearly relate. But the relationship itself is an entity with its own describing elements. The relationship links to a Movie, and links to an Actor, but also contains information like "Character Name" or "Contract Salary".

Note: This approach doesn't really seem to make sense, at least semantically, for your domain. How can one Item simultaneously exist in multiple Boxes?

edited Nov 20, 2015 at 16:16

answered Nov 20, 2015 at 15:59

David

221k42 gold badges245 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Koosshh56 Over a year ago

I know concept of primary key, foreign key and also relationships. The problem here is that I would like to use more items for more boxes and it would be many to many relationship.

Ryan Searle Over a year ago

You cannot have a many to many relationship in a relational database without an intermediary table.

Koosshh56 Over a year ago

I know, this is my problem. I need a link table. But what I should put in this table.

David Over a year ago

@Koosshh56: In that case some renaming would clarify things. Instead of Box and Item, you would have things like Order and Product. The linking table could be OrderProduct or, more semantically, something like LineItem. An Order doesn't contain Products, but it does reference them. And that reference (the linking table) would contain information about the relationship itself. "Quantity" would be a good example.

David Over a year ago

@Koosshh56: An auto-incrementing ID from the database is usually the simplest approach unless there's a compelling reason to use something else.

|

Ryan Searle · Accepted Answer · 2015-11-20 16:07:03Z

2

First of all you will need at least two tables, one for boxes and one for items. If one item can belong to more than one box as well as one box having many items you will need an intermediary table (see below):

In the container tables you should have:

ContainerID
FK_BoxID
FK_ItemID
etc...

In the Boxes tables you will need:

BoxID
[box related fields]

In the Items table you will need:

ItemID
[Item related fields]

answered Nov 20, 2015 at 16:07

Ryan Searle

1,6271 gold badge20 silver badges30 bronze badges

11 Comments

Cetin Basoz Over a year ago

Why "at least" 2? At least 1 is fine.

Ryan Searle Over a year ago

In fact you need at least 3 for a proper relationship between the two in a relational database.

Koosshh56 Over a year ago

Ok, so I have to use an ID for Item as well. And the link table is using boxId and itemId as foreign key. This is really clear, thanks

Cetin Basoz Over a year ago

Where did you reason he was talking about an RDBMS? Even so there are RDBMS that can do this in a single table. Based on your assumption and traditional ways of modeling this, you are not really wrong.

Ryan Searle Over a year ago

'I'm a beginner, especially in databases within c#' - It was a fair assumption that he wants an RDB. Yes you can design a database to use as few tables as possible however this doesn't achieve much good in his situation.

|

wdoering · Accepted Answer · 2015-11-20 16:08:37Z

0

If you want to have a one-to-many relationship your classes would look like this:

public class Box
{
    public Box() { }

    public int BoxId { get; set; }
    public string BoxName { get; set; }

    public virtual Item Item { get; set; }
}

public class Item
{
    public Item()
    {
        Boxes = new List<Box>();
    }
    public int ItemId { get; set; }
    public string Description { get; set; }

    public virtual ICollection<Box> Boxes { get; set; }
}

Follow the article below, it also explains how to create foreign keys and everything.

http://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx

answered Nov 20, 2015 at 16:08

wdoering

3316 silver badges16 bronze badges

Collectives™ on Stack Overflow

Database list of objects as property of class

3 Answers 3

14 Comments

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

14 Comments

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related