StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPros and Cons of different Schema designs for tables that all have a relationship to one data item
text
Body
copied!<p>I am developing a system that will have a database backend. I am intending that every table have a PK that is arbitary, system generated and maintained.</p> <p>My environment (for development and production) is Windows 7; Delphi; and an embedded database (probably Firebird).</p> <p>My data structures include one table OWNER, that has very little information associated with it - probably no more than name and description. The rest of the data structure will be about 50 tables, each with name and description, together with a list of other attributes - say 20 per table on average. In addition there could be 20 associative tables most of which will have only FK attributes, though a small proportion will have additional attributes. It is my intention (at least to start with) to have the schema fully normalised. </p> <p>For a given OWNER, most table will have O(10^3 to 10^4) records, though one or two will have O(10^5 to 10^6). The number of OWNERs is likely to be O(10^2 to 10^3). Most accesses are likely to be clustered - there will be a substantial number of accessess for one OWNER, and then a substantial number of accesses for another OWNER.</p> <p>Every data item will belong to exactly one OWNER, and cannot be transferred from OWNER to OWNER. All accesses to the database will know what OWNER they are using; no access will ever have to access the contents of more than one OWNER. </p> <p>I am aware of the following three options in designing my schema:</p> <ol> <li>Use the OWNER table as described. Use the OWNER PK as a foreign key in every table (though possibly not the associative tables). Add the OWNER PK as an additional clause into every query, join, stored procedure, view etc.</li> <li>Add a column to the OWNER table containing a small code - say a four digit integer. For each OWNER, create a duplicate set of tables - the table names for a particular OWNER will have the appropriate code added as a suffix. This would require every access to have previously obtained the suffix code from the database. Then the access will be of the relevant set of tables.</li> <li>For each OWNER, create a duplicated database, with the tables and table names being duplicated. This implies that there would probably be a subsidiary common database containing data about the relevant duplicate database. Again this common database would need accessing before any access - or series of accesses for one particular OWNER.</li> </ol> <p>What are the pros and cons of these different approaches? Have I missed any other options for overall design?</p> <p><strong>Edit reversed - I provided my own answer instead.</strong></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