DataSet 1 to many Relation - How to?

[Sharon]

I have two tables in the DataSet, the first present rooms and the other
furniture kinds.

Furniture kind, like chare, can be in more then one room. And specific room
can contain more then one furniture kind (chare + table + ...). Therefore,
the room table should have Relation that point to several furniture kind, and
the furniture table should have Relation to several rooms.

Is there a way to define this kind of relation from one to many?
If so - How?

Any advice will be highly appreciated.
---------
Thanks
Sharon G.

[Ignacio Machin \( .NET/ C# MVP \)]

hi,

Really it's a many to many relationship and you need an extra table to hold
this. this table will have a FK to both rooms & furniture kinds
cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Sharon" <Sh****@discuss ions.microsoft. com> wrote in message
news:6E******** *************** ***********@mic rosoft.com...

I have two tables in the DataSet, the first present rooms and the other
furniture kinds.

Furniture kind, like chare, can be in more then one room. And specific
room
can contain more then one furniture kind (chare + table + ...). Therefore,
the room table should have Relation that point to several furniture kind,
and
the furniture table should have Relation to several rooms.

Is there a way to define this kind of relation from one to many?
If so - How?

Any advice will be highly appreciated.
---------
Thanks
Sharon G.

[Sharon]

Can you please axplain how do I do that?

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

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

You create a table with two column, each column is a FK to the PK of the
other tables (furniture, room ), Note that they both "may" not be declare
as PK ( a room with more than one particular kind fo furniture) so either
you declare another column or just work the table without PK.

Now, I have never used the designer from VS.NET so I don't know for sure how
to do it, I use XDE there you select the relationship ( many to many ) and
just click two tables, a new table & the relationships will be generated.
cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Sharon" <Sh****@discuss ions.microsoft. com> wrote in message
news:DD******** *************** ***********@mic rosoft.com...

Can you please axplain how do I do that?

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

[Sharon]

After looking further to the solution you have suggested, I'm puzzled with
two unclear questions:
(1) If I have 3 rooms and 12 furniture all together, 4 furniture in each
room. How many ROOM_FURNITURE tables will be created in this case that will
hold the bi-directional relations between the rooms and the furniture
(relations that point from furniture to room and from room to furniture) ?
How the ROOM_FURNITURE will look like?

(2) Lets say I'm trying to find the furniture that room 3 contains.
So, I go from room 3 row through its key to the ROOM_FURNITURE row in the
ROOM_FURNITURE table, but now I need to find to which furniture to go to
because there are many furniture in that room or even many furniture for all
the other rooms.
-------
Thanks
Sharon G.

[Michael Voss]

Sharon wrote:

After looking further to the solution you have suggested, I'm puzzled with
two unclear questions:
(1) If I have 3 rooms and 12 furniture all together, 4 furniture in each
room. How many ROOM_FURNITURE tables will be created in this case that will hold the bi-directional relations between the rooms and the furniture
(relations that point from furniture to room and from room to furniture) ?
How the ROOM_FURNITURE will look like?
The link table will look somewhat like:

room_id
furniture_kind_ id
kind_count (if you have more than one chair in a room)

There will be a row for each kind of furniture in each room:

room_id furniture_kind_ id kind_count
=============== =============== =
3 1 1
3 2 2
3 3 1
4 1 2
4 2 4
....

with a furniture_kind_ table like

id name
========
1 bed
2 chair
3 table
(2) Lets say I'm trying to find the furniture that room 3 contains.
So, I go from room 3 row through its key to the ROOM_FURNITURE row in the
ROOM_FURNITURE table, but now I need to find to which furniture to go to
because there are many furniture in that room or even many furniture for all the other rooms.

You'd select all rows from the link table with room_id = 3. In the above
example, that would be three rows:

room_id furniture_kind_ id kind_count
=============== =============== =
3 1 1
3 2 2
3 3 1

From each furniture_kind, (1, 2 or 3), you can then select the appropriate
entry of the furniture_kind_ table.

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Responses to your questions are inline.

Now I have a question, does the furniture table hold furniture types or
furnitures instances, please note they are different, a Furniture type are
generics entities ( sofa, chair, loveseat, etc ) with certain properties. A
furniture instance is the black leather sofa placed in the livingroom.

I hope you see the difference. I can show you a UML model with both
escenarios if you need further clarification.

"Sharon" <Sh****@discuss ions.microsoft. com> wrote in message
news:CB******** *************** ***********@mic rosoft.com...

After looking further to the solution you have suggested, I'm puzzled with
two unclear questions:
(1) If I have 3 rooms and 12 furniture all together, 4 furniture in each
room. How many ROOM_FURNITURE tables will be created in this case that
will
hold the bi-directional relations between the rooms and the furniture
(relations that point from furniture to room and from room to furniture) ?
How the ROOM_FURNITURE will look like?
Only one table !!!
with 3*4 rows
Will look like this ( I scripted a piece of one DB and changed the names of
tables/columns , hope it will be clear enough )

ALTER TABLE [dbo].[Room_Furniture] ADD
CONSTRAINT [Constraint19] PRIMARY KEY NONCLUSTERED
(
[roomID],
[furnitureTypeID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Room_Furniture] ADD
CONSTRAINT [Constraint18] FOREIGN KEY
(
[roomID]
) REFERENCES [dbo].[RoomTable] (
[pID]
),
CONSTRAINT [Constraint20] FOREIGN KEY
(
[furnitureTypeID]
) REFERENCES [dbo].[FurnitureType] (
[uID]
)
GO
(2) Lets say I'm trying to find the furniture that room 3 contains.
So, I go from room 3 row through its key to the ROOM_FURNITURE row in the
ROOM_FURNITURE table, but now I need to find to which furniture to go to
because there are many furniture in that room or even many furniture for
all
the other rooms.

You can use any of these queries:

SELECT * FROM FurnitureType WHERE FurnitureTypeID in ( SELECT
FurnitureTypeID FROM Room_Furniture WHERE roomID = 3 )

or this other:

SELECT Furniture.* FROM FurnitureType, Room_Furniture WHERE
FurnitureType.F urnitureTypeID = Room_Furniture. FurnitureTypeID AND
Room_Furniture. RoomID = 3


cheeers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[Sharon]

Thanks for the answers.

Ignacio - The furniture table hold furniture types.
Michael & Ignacio - I can see now how its work, but as I see it it's very
un-efficient, because the select need to search for all rows with value of
room 3.

Can I make it more efficient? I have heard that index will help. Can you
shade some light on this issue?

-------------
Thanks again
Sharon G.

[Michael Voss]

Sharon wrote:

Michael & Ignacio - I can see now how its work, but as I see it it's very
un-efficient, because the select need to search for all rows with value of
room 3.
Why do you consider this inefficent ? You'd just select everything from that
table "where room_id = 3"; this would return the three rows.

Can I make it more efficient? I have heard that index will help. Can you
shade some light on this issue?
If the selects are too slow, setting an index on each id-column (room_id and
funriture_kind_ id) will help most likely.


-------------
Thanks again
Sharon G.

[Sharon]

That's good I thought so too.

can you explain what the indexing mean and how would I add it?
--------------
Thanks a lot
Sharon G.