Multisite Enabling a Table
- by Joe Fitzgibbons
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?
