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.

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" <(E-Mail Removed)> wrote in message
news:6ECA298B-2A7D-4E03-B997-(E-Mail Removed)...
>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.

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.

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" <(E-Mail Removed)> wrote in message
newsD1289A6-D4CF-4C2B-842B-(E-Mail Removed)...
> 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.

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.

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.

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" <(E-Mail Removed)> wrote in message
news:CB1B1C51-FDE5-45A2-AC8B-(E-Mail Removed)...
> 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,

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

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.

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.

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.