Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This is called the Observation Pattern.</p> <p><a href="https://i.stack.imgur.com/L8rKm.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/L8rKm.png" alt="enter image description here"></a></p> <p>Three objects, for the example</p> <pre><code>Book Title = 'Gone with the Wind' Author = 'Margaret Mitchell' ISBN = '978-1416548898' Cat Name = 'Phoebe' Color = 'Gray' TailLength = 9 'inch' Beer Bottle Volume = 500 'ml' Color = 'Green' </code></pre> <p>This is how tables may look like:</p> <pre><code>Entity EntityID Name Description 1 'Book' 'To read' 2 'Cat' 'Fury cat' 3 'Beer Bottle' 'To ship beer in' </code></pre> <p>.</p> <pre><code>PropertyType PropertyTypeID Name IsTrait Description 1 'Height' 'NO' 'For anything that has height' 2 'Width' 'NO' 'For anything that has width' 3 'Volume' 'NO' 'For things that can have volume' 4 'Title' 'YES' 'Some stuff has title' 5 'Author' 'YES' 'Things can be authored' 6 'Color' 'YES' 'Color of things' 7 'ISBN' 'YES' 'Books would need this' 8 'TailLength' 'NO' 'For stuff that has long tails' 9 'Name' 'YES' 'Name of things' </code></pre> <p>.</p> <pre><code>Property PropertyID EntityID PropertyTypeID 1 1 4 -- book, title 2 1 5 -- book, author 3 1 7 -- book, isbn 4 2 9 -- cat, name 5 2 6 -- cat, color 6 2 8 -- cat, tail length 7 3 3 -- beer bottle, volume 8 3 6 -- beer bottle, color </code></pre> <p>.</p> <pre><code>Measurement PropertyID Unit Value 6 'inch' 9 -- cat, tail length 7 'ml' 500 -- beer bottle, volume </code></pre> <p>.</p> <pre><code>Trait PropertyID Value 1 'Gone with the Wind' -- book, title 2 'Margaret Mitchell' -- book, author 3 '978-1416548898' -- book, isbn 4 'Phoebe' -- cat, name 5 'Gray' -- cat, color 8 'Green' -- beer bottle, color </code></pre> <p><strong>EDIT:</strong></p> <p>Jefferey raised a valid point (see comment), so I'll expand the answer.</p> <p>The model allows for dynamic (on-fly) creation of any number of entites with any type of properties without schema changes. Hovewer, this flexibility has a price -- storing and searching is slower and more complex than in a usual table design. </p> <p>Time for an example, but first, to make things easier, I'll flatten the model into a view.</p> <pre><code>create view vModel as select e.EntityId , x.Name as PropertyName , m.Value as MeasurementValue , m.Unit , t.Value as TraitValue from Entity as e join Property as p on p.EntityID = p.EntityID join PropertyType as x on x.PropertyTypeId = p.PropertyTypeId left join Measurement as m on m.PropertyId = p.PropertyId left join Trait as t on t.PropertyId = p.PropertyId ; </code></pre> <p>To use Jefferey's example from the comment</p> <pre><code>with q_00 as ( -- all books select EntityID from vModel where PropertyName = 'object type' and TraitValue = 'book' ), q_01 as ( -- all US books select EntityID from vModel as a join q_00 as b on b.EntityID = a.EntityID where PropertyName = 'publisher country' and TraitValue = 'US' ), q_02 as ( -- all US books published in 2008 select EntityID from vModel as a join q_01 as b on b.EntityID = a.EntityID where PropertyName = 'year published' and MeasurementValue = 2008 ), q_03 as ( -- all US books published in 2008 not discontinued select EntityID from vModel as a join q_02 as b on b.EntityID = a.EntityID where PropertyName = 'is discontinued' and TraitValue = 'no' ), q_04 as ( -- all US books published in 2008 not discontinued that cost less than $50 select EntityID from vModel as a join q_03 as b on b.EntityID = a.EntityID where PropertyName = 'price' and MeasurementValue &lt; 50 and MeasurementUnit = 'USD' ) select EntityID , max(case PropertyName when 'title' than TraitValue else null end) as Title , max(case PropertyName when 'ISBN' than TraitValue else null end) as ISBN from vModel as a join q_04 as b on b.EntityID = a.EntityID group by EntityID ; </code></pre> <p>This looks complicated to write, but on a closer inspection you may notice a pattern in CTEs.</p> <p>Now suppose we have a standard fixed schema design where each object property has its own column. The query would look something like:</p> <pre><code>select EntityID, Title, ISBN from vModel WHERE ObjectType = 'book' and PublisherCountry = 'US' and YearPublished = 2008 and IsDiscontinued = 'no' and Price &lt; 50 and Currency = 'USD' ; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POdesigning database to hold different metadata information
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USDamir Sudarevic
    1. USDamir Sudarevic
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POdesigning database to hold different metadata information
      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. COThanks this is a very good answer and a lead. Let's add more to this discussion. In this sort of design how would one account for fast searching? I would imagine that would require a lot of joins ??
      singulars
      1. PO
      1. USObaid
    2. COThis is a very bad idea, and it will lead to problems further down the road. Please don't do this.
      singulars
      1. PO
      1. USJeffrey L Whitledge
    3. COFor example, try getting all the books from US publishers of academic books published in 2008 that haven't been discontinued and cost less than $50. Good luck! In a properly designed relational database that's a 2-minute task.
      singulars
      1. PO
      1. USJeffrey L Whitledge
 

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