DataSet 1 to many Relation - How to?

[Guest]

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.

 

[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,

 

[Guest]

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.

 

[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,

 

[Guest]

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.

 

[Michael Voss]

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.

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.FurnitureTypeID = Room_Furniture.FurnitureTypeID AND
Room_Furniture.RoomID = 3




cheeers,

 

[Guest]

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?

 

[Michael Voss]

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.

 

[Guest]

That's good I thought so too.

can you explain what the indexing mean and how would I add it?

 

[Michael Voss]

That's good I thought so too.

can you explain what the indexing mean and how would I add it?

[...snip...]

Where do you store your data ?

 

[Guest]

In first stage of the project I'll will store the data in a XML file.
And later on, in the second stage it may be stored in a SQL server.

 

[Michael Voss]

In first stage of the project I'll will store the data in a XML file.
And later on, in the second stage it may be stored in a SQL server.

Indexing will help you on the database, but not on a dataset...

 

[Guest]

OK, I see what you mean.

But when I will use the SQL server then I will use the indexing. For that do
I need to do any setting in the DataSet or where?
If it is in the DataSet, can you tell me how to do so?

 

[Michael Voss]

OK, I see what you mean.

But when I will use the SQL server then I will use the indexing. For that do
I need to do any setting in the DataSet or where?
If it is in the DataSet, can you tell me how to do so?

An index is used on the database only. But you'll have to make sure your
selects are build to use the indexes of you table. If you look at your link
table, you'll propably have at least two columns: room_id and furniture_id.
If you'd create only index on room_id and (!) furniture_id (in that order),
this index would not help you if you'd select "where furniture_id =
something", because the index is on room_id first. You would be performing a
full table scan, which is (relatively) slow. If you'd select "where room_id
= something", the index would be useful, because the first index column can
be used. Your database must perform an index scan only, which is
(relatively) fast. So your indexes must match your where-clauses.

But this is database stuff and somewhat OT; maybe you'd be better off asking
this kind of stuff in a SQLServer or dotnet.database newsgroup. Anyway, I
wouldn't mind answering here...