Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Ok, you've hit a brick wall. And you realise that actually the db design has problems. And you are going to keep hitting this same brick wall many times in the future. And your future is not looking bright. And you want to change that. Good.</p> <p>But what you have not yet done is, figure what the actual cause of this is. You cannot escape from the predictable future until you do that. And if you do that properly, there will not be a brick wall, at least not this particular brick wall.</p> <p>First, you went and stuck<code>Id</code>iot columns on all the tables to force uniqueness, without really understanding the <strong>Identifiers</strong> and keys that used naturally to find the data. That is the bricks that the wall is made from. That was an unconsidered knee-jerk reaction to a problem that demanded consideration. That is what you will have to re-visit.</p> <ol> <li><p>Do not repeat the same mistake again. Whacking GUIDs or UUIDs, or 32-byte<code>Id</code>iot columns to fix your<code>NUMERIC(10,0) Id</code>iot columns will not do anything, except make the db much fatter, and <strong>all</strong> accesses, especially joins, much slower. The wall will be made of concrete blocks and it will hit you every hour.</p></li> <li><p>Go back and look at the tables, and design them with a view to being tables, in a database. That means your starting point is <strong>No Surrrogate Keys</strong>, no<code>Id</code>iot columns. When you are done, you will have very few<code>Id</code> columns. Not zero, not all tables, but very few. Therefore you have very few bricks in the wall. I have recently posted a detailed set of steps required, so please refer to:</p> <p><a href="https://stackoverflow.com/questions/4275954/how-to-identify-duplicate-items-gathered-from-multiple-feeds-and-link-to-them-in/4284899#4284899"><strong>Link to Answer re Identifiers</strong></a></p></li> <li><p>What is the justification of having one audit table containing the audit "records" of all tables ? Do you enjoy meeting brick walls ? Do you want the concurrency and the speed of the db to be bottlenecked on the Insert hot-spot in one file ? </p> <p>Audit requirements have been implemented in dbs for over 40 years, so the chances of your users having some other requirement <em>that will not change</em> is not very high. May as well do it properly. The only correct method (for a Rdb) for audit tables, is to have one audit table per auditable real table. The PK will be the original table PK plus DateTime (Compound keys are normal in a modern database). Additional columns will be UserId and Action. The row itself will be the before image (the new image is the single current row in the main table). Use the exact same column names. Do not pack it into one gigantic string.</p> <p>If you do not need the data (before image), then <em>stop recording it</em>. It is a very silly to be recording all that volume for no reason. Recovery can be obtained from the backups.</p></li> <li><p>Yes, a single <code>RecordKey</code> table is a monstrosity. And yet another guaranteed method of single-threading the database.</p></li> </ol> <p>Do not react to my post, I can already see from your comments that you have all the "right" reasons for doing the wrong thing, and keeping your brick walls intact. I am trying to help you destroy them. Consider it carefully for a few days before responding.</p>
    singulars
    1. This table or related slice is empty.
    1. POPrimary keys, UUIDs, RecordKey tables, oh my
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USPerformanceDBA
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POPrimary keys, UUIDs, RecordKey tables, oh my
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COback to the roots. +1
      singulars
      1. PO
      1. USPatrick Honorez
    2. CO@iDevelop. Thanks. That's exactly the right term. Without roots, the tree just falls over. If you model the data using the IDEF1X Relational modelling Standard, those roots, the Identifiers, are actually **visible** in the Data Model.
      singulars
      1. PO
      1. USPerformanceDBA
    3. COThanks **PerformanceDBA**; I appreciate your direct and forward perspective. I have redesigned my tables a few times and despite having gone with a BINARY(16) key where (I felt) necessary, I have still been exploring my options. Your feedback has provoked me to research and reconsider my design, and I will be reading up on IDEF1X Relational Modeling Standard as mentioned in your last comment. Lollers at id-iot :P
      singulars
      1. PO
      1. USDan Lugg
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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