StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Table Matrix
text
Body
copied!<p>Forgive me if the terminology of the title is incorrect, what im describing is an intermediate table which enables a kind of many-to-many relationship between 2 or more tables.</p> <p>In this case, a single table holds data which applies to other tables which aren't necessarily related to each other.</p> <p>Specifically a site:</p> <ul> <li>Page A and B from table Pages belongs to Site 1. </li> <li>User A from table Users also belongs to Site 1, but User B belongs to Site 2</li> </ul> <p>So the middle table at the moment looks like this:</p> <pre><code>id | site | page | user ----------------------- 1 | 1 | A | null 2 | 1 | B | null 3 | 1 | null | A 4 | 2 | null | B </code></pre> <p>Despite this appearing to become a cause of many problems, the system would be supported by the database being fully normalized, the deletion of a user for example would delete any record associated with it in the matrix, updates would also cascade.</p> <p>I understand that the amount of data in here could get pretty high, which is why it would only be used to link reference tables containing between 5-200 records. Mass record tables such as posts or media containing upwards of several thousand records and growing that are connected to sites for example would be flattened and store the site ID directly.</p> <p>Finally the reason I need a solution like this is because I need the elements to be assigned to a customizable number of sites, otherwise the only options are: 1 site, all sites, or no sites.</p> <p>So my question is, is this an effective method?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload