So I have many rooms
that can contain any number of devices
. Those devices
can be in many rooms
. This is straightforward sofar. Now I need to be able to add multiple instances of the same device
to a single room
. That is, a given room might have multiple instances of the same projector model.
This is proving surprisingly difficult to do. My first thought was to add a simple count
column to the join table, but according to this answer, doing so isn't straightforward. Maybe I'll return to this solution, but for now I'd like to look at a plan B.
Plan B would just involve adding multiple rows containing the same device in the same room. Something like this...
for(var i = 0; i < newDevice.count; i++)
{
room.Devices.Add(device);
}
...which doesn't work. `.Add(device) will only add the one row, no matter how many times I loop through. This question might be relevant.
How would I go about adding multiple instances of the same row to the Rooms_Devices
table in this scenario?
EDIT FOR CLARITY:
What I am looking for is a way to create multiple rows of the the same data to my many-to-many table. That is, I want my above snippet to produce rows similar to this:
Rooms_Devices
---------------------------
id | room_id | device_id
1 | 22 | 38 <--- same room, same device, three times
2 | 22 | 38
3 | 22 | 38
The code above instead produces this:
Rooms_Devices
---------------------------
id | room_id | device_id
1 | 22 | 38 <----just one row, not three
The problem being .Add()
doesn't want to create the same row multiple times (apparently). I am using Entity Framework.
EDIT FOR ANSWER:
The answer below is correct, but it didn't make immediate sense to me, so I'll summarize: In a many-to-many join table, the id pair (room_id
, device_id
) must be unique, so what I was trying to do can't be done. If you want to add a column to a many-to-many relationship, you have to build a model for the table. That is, I now have a Devices_Rooms
model. This necessitates changing a bunch of other code (and making that code more difficult to read), but it appears to be the only way to go.
Answer
That is, a given room might have multiple instances of the same
projector model.
If I understand you then you will want to consider a slightly different design. Your devices table would have one row for every physical device. The same physical device can not be in the same room twice and for that matter can not be in two rooms at the same time.
You can have a device type table, where each model or model group (for similar models) has one row.
Then it is easy to do a query grouping on room, devicetype, count without ever having to update the 'count' field on the associative entity (RoomsDevices).
The data would look like this when joined to device type, which is keyed off the device table. I will add it as if it were joined for demonstration:
Rooms_Devices
-------------------------------------------
id | room_id | device_id | devicetypeid
1 | 22 | 3 | 38
2 | 22 | 4 | 38
3 | 22 | 5 | 38
A query on this would yield:
room_id | devicetype_id | Count
22 | 38 | 3
No comments:
Post a Comment