What is the correct way to store an array / tuple of id's in a column in SQL Server

Question

I am redesigning a local IP management site, and need some help. Currently, there is a foreign key building ID associated with a certain subnet ID, so that the client knows which building the subnet is in. However, a problem that has risen is that a subnet may be split across multiple buildings. Now that the subnet-to-building paradigm no longer holds, I am at a loss for the correct way to manage this.

One option that comes immediately to mind and just seems bad is to change the building id field to a string of comma-separated building ID's. It's quick and it works, but it gives me a bad feeling.

Another idea that has come to mind is creating a BuildingIDTuple table, where multiple rows may exist mapping a subnet id to a buildingid.

subnet id | building id
------------------------
    1     |      2 
    1     |      3

This method seems cleaner and more future-proof, but I can also imagine the sql queries causing many headaches.

The ideal method I would think is to have an array of building ID's in the subnet table, but I don't think those exist.

So, what would be the best way to design what I am doing? Any help would be very much appreciated!

Answer 1

Just create the extra table, the required join will never be that complicated.

Parsing a comma-separated list or whatever will be what gives you a headache.

Answer 2

never store a set of values in a single database column!! break them out into a new table, each value as an individual row!! Otherwise you will be a slave to splitting those CSV values out each time you query them. Your BuildingIDTuple table is the way to go. The queries are not harder, but you will need to support possible multiple rows comming back for each subnet.