Databases
StackOverflow2013
Postsdbo
POWhat type of database table structure is this called?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat type of database table structure is this called?
    text
    copied!<p>A long time ago I took over maintenance of a web based content management system that had a pretty clever database table in it.</p> <p>The table structure was like:</p> <p><strong>TABLE: tPage</strong> (PK on PageID, Version, Status)</p> <pre><code>PageID, PageTitle, Version, Status 1, Homepage, 1, Archived 1, Homepage, 2, Live 1, New Home, 3, Draft </code></pre> <p><strong>TABLE: tElements</strong> (PK on PageID, Version, ElementName)</p> <pre><code>PageID, Version, ElementName, ElementValue (nText) 1, 1, IntroText, Hi, this is our first version. 1, 2, IntroText, This is the current version, 1, 2, MainBody, We sell sprockets. 1, 3, IntroText, Welcome text here 1, 3, MainBody, We sell sprockets and widgets. </code></pre> <p>To get the main body text of the current homepage you'd write a query like:</p> <p><strong>Query:</strong></p> <pre><code>SELECT ElementValue FROM tElements, tPage WHERE PageID = 100 ElementName = 'MainBody' AND tPage.PageID = tElements.PageID AND tPage.Version = tElements.Version AND tPage.Status = 'Live' </code></pre> <p><strong>Result:</strong></p> <pre><code>We sell sprockets. </code></pre> <p>The beauty of this table structure, (to me at least) was that I didn't need to add new columns to a table when I wanted to add new types of data. I just created a new ElementName. But functionally it was like a new column.</p> <p>I've since used this arrangement in a few other projects where I needed a database that would change easily as new features/content types would be added. For example, in my above CMS example, we started with just IntroText, and MainBody, but by a few years later we ended up with CoursePrice, ContactPerson, RelatedCourses, SetupFee, and I found this way of organising our data to be very flexible.</p> <p>My question is, what is this type of table structure called? I'm not really sure what keywords I should be googling for more information.</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