Representing a list in a SQL database

Question

I'm doing an application in C# with an SQL database.

I've got a model:

Person()
{
  string id;//unike key
  string name;
  List<string> responsableOf;//list of id
}

and I want to represent it into a table.

Which are the right fields for the tables?

Answer 1

It depends on what kind of relation is there between the person and the other persons that he will be responsible for.

If it is a parent and child relation i.e a composition you can use a self reference table. Something like:

Persons with the following columns:

• Id,
• name.
• ParentId Foreign key to the same table.

If the relation between the person and the others is an aggregation, and a person may be responsible for many other persons:

Persons:

• Id,
• name.

usersresponsibilities:

• Id,
• PersonId,
• ResobonsiblePersonID.

Later, in both the two cases, in your front end application you will need to deal with the data in these table as an objects not as rows. You should think in terms of objects.

In your application your current class Person should be mapped to this table.

Answer 2

The solution is going to depend a little on your constraints :

If a single person can have several people responsible for them

What you need is a many to many relationship between persons (and a table for the person containing the other fields, ID and name)

You would represent that using a table associating two IDs (Foreign Keys) For any given row, the table would associate :

• The ID of the responsible
• The ID of a person he is responsible for

Building your list is as simple as querying this table for rows with a given responsible ID.

If a single person can only have one person responsible for them

You need a Foreign Key to the same table in your Person table (Responsible ID).

Building your list is then as easy as querying the person table for rows with a given responsible ID.