DataSet 1 to many Relation - How to?

You describe a Many-To-Many relation.
You will need to define two tables with primary key, and a relation table with a primary key consisting of two forign keys for the other tables.
I your case a ROOM table, a FURNITURE table, and a ROOM_FORNITURE table. Each raw in the latter table has one key from ROOM and one key from FURNITURE.

jhammer - I'm not sure I understand (I'm new in the DataSet area).

Can you give some more details/sample/post ?

Why do you say that I need many-to many relation and not one-to many?

Note: I'm designing a model using XSD file that the VS .NET generate a DataSet object from it.


---
Thanks
Sharon G.

jhammer - I'm not sure I understand (I'm new in the DataSet area).

Can you give some more details/sample/post ?

Why do you say that I need many-to many relation and not one-to many?

Note: I'm designing a model using XSD file that the VS .NET generate a DataSet object from it.


---
Thanks
Sharon G.

Well, if you just open the xsd file in the designer, right click the table you want to create a relation for, select add - relation, define primary key if you not already you have done so, then you are done.

And I describe 1 to many relation. DataRelation are for one to many (parent-child).

Thanks a lot to you two for the input.

jhammer -
I think I'm missing something.
I already have the two tables ROOM and FURNITURE and each table contain one key RoomKey and FurnitureKey, and from what I understand, I need to add another element (table) to XSD diagram - that's fine.
But what this element should contain:
(1) How many elements?
(2) What type should I select for each element.
(3) What relation and how should I add? between which tables? I have 3 now.

Klintan - now that I know that the Relation is one-to-many/many-to-many, how do I set it to point from one room to several Furniture?

---
Thanks
Sharon G.

DataRelation is one to many (not many to many).

Assume you have created a schema like in the attached file, code could look like this to define relations:

Dataset1 ds=new Dataset1();
Dataset1.FurnitureTypeRow chair=ds.FurnitureType.NewFurnitureTypeRow();
chair.FurnitureTypeId=1;
chair.Description="Chair";

Dataset1.FurnitureTypeRow table=ds.FurnitureType.NewFurnitureTypeRow();
table.FurnitureTypeId=2;
table.Description="Table";

Dataset1.RoomRow livingRoom=ds.Room.NewRoomRow();
livingRoom.RoomId=1;
livingRoom.Description="Living Room";

Dataset1.RoomRow kitchen=ds.Room.NewRoomRow();
kitchen.RoomId=2;
kitchen.Description="Kitchen";

Dataset1.FurnitureRow chairInKitchen=ds.Furniture.NewFurnitureRow();
chairInKitchen.FurnitureTypeId=chair.FurnitureTypeId;
chairInKitchen.RoomId=kitchen.RoomId;
chairInKitchen.DatePurchased=new DateTime(2005,06,09);

(Just to confuse things I have the FurnitureType for the type of furniture (e.g. Chair) and Furniture for the instance (e.g. the chair in the kitchen), where Jhammer has Furniture and Furniture_Room for the same thing.)

Datasets can be connected to database using data adapters, bound to controls, you can define auto-increment for the key columns so that you don't have to bother setting them, and do lots of other stuff that I not have shown here.

Here is an example of navigation:

// Loop through all furniture types
foreach (Dataset1.FurnitureTypeRow furnitureType in ds.FurnitureType)
{
Console.Write("Rooms that have a {0}: ",furnitureType.Description);
foreach (Dataset1.FurnitureRow furniture in furnitureType.GetFurnitureRows())
{
Console.Write("{0}, ",furniture.RoomRow.Description);
}
Console.WriteLine();
}

I am sorry to say that Many-To-Many relations are complicated. There is no easier way to navigate (at least I don't know of one).

1) You will have 12 rows in ROOM_FURNITURE, as each row represents a concrete furniture (e.g. the green sofa in the living room), while the FURNITURE table contains furniture types (e.g. sofa)

2) When you go from room 3 row to its key in the ROOM_FURNITURE table, you find only the ROOM_FURNITURE rows for the furniture in room 3 (which can be several, but it cannot be furniture in other room per definition).

I saying the same thing all the way:
I have a table that hold furniture types/kind (chair, table, closet etc.), and a table of rooms (room 1, room 2, ...). Any room can can contain any kind of furniture, meaning that room 2 and 3 may hold chairs so a chair is present in room 2 and 3.

I also posted an example for 12 kind of furniture, that are located in 3 separate rooms so each room contain 4 kind of furniture.

Any way, thanks a lot for the help. I finally understand this issue, I'm simple new at the DB area.


-----
Thanks again
Sharon G.