Wednesday, 14 September 2016

asp.net - Many-to-many relationships with multiple instances of the same item




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

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...