I am creating a table (table A) that will have a number of columns(of course) and there will be another table (table B) that holds metadata associated to rows in table A.

I am working with a multi site implementation that has one database for the whole shabang. Rows in table A could belong to any number of sites but must belong to at least one.

The problem I have is I am not sure what the best practice is for defining what site each row in table A belongs to. I want performance and scalability. There is no finite number of sites going forward. Rows in table A could belong to any number of sites in the future. Right now there are only 3.

My initial thoughts are to have a primary site ID in Table A and then metadata in table B will have rows defining additional sites as needed.

Another thought is to have a column in Table A for each site and it is a boolean as to wether it belongs to that site.

Lastly I have thought about having another table to map rows in Table A to each site.

What is the best way to associate rows in a table with any number of sites with performance and scalability in mind?