0

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.

Community
  • 1
  • 1
crowhill
  • 1,996
  • 2
  • 17
  • 47
  • It seems like adding a row_number in your SQL query ROW_NUMBER() OVER (PARTITION BY room ORDER BY ??? ) could solve your issue as it adds a count by room to your result set. But I am not sure I am understanding how you plan on using the information. – Matt Jul 18 '16 at 16:46
  • Right now, _getting_ the information isn't the issue. I don't know how to _create_ the rows in the table. Am I understanding the comment? – crowhill Jul 18 '16 at 16:48
  • So you are more worried about how to maintain the data. based on the link in your question it appears you are probably using entity framework. If you don't do it by a count column you would just maintain a identity key on your many to many relationship table in your code and pass that identity to the delete/update statement and insert will automatically return it to you. If you want to do it by count you will just need to customize the update, insert, delete statements to do the appropriate command (e.g. update instead of insert when a record exists and increment by 1) – Matt Jul 18 '16 at 16:57
  • 1
    I don't think I was clear enough. I edited my question. – crowhill Jul 18 '16 at 17:07

1 Answers1

2

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
Joe C
  • 3,835
  • 2
  • 9
  • 28
  • Right now I really don't want to re-engineer the table structure. The application is already in production and it would make my life a lot easier if I didn't have to modify it. That said, the `Devices` table is where the "device prototype" exists. `Rooms_Devices` is where the "actual" (or "physical") device is created. I'm not certain what the third table would contribute. Could you clarify? – crowhill Jul 18 '16 at 17:43
  • Basically a many to many relationship will not allow duplicate data on the two keys. By definition this has to be a unique value (rooms and devices). When trying to use a framework for an application, I am guessing EF since you are doing asp.net, the framework works on these types of standard database design to automate (scaffold) code in the repository/service layer. non-traditional designs will require extra work in custom coding the implementation of these frameworks. – Joe C Jul 18 '16 at 17:52