Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Perhaps you should look <a href="https://stackoverflow.com/q/695752/119477">this question</a></p> <p>The accepted answer from Bill Karwin goes into specific arguments against the key/value table usually know as Entity Attribute Value (EVA)</p> <blockquote> <p>.. Although many people seem to favor EAV, I don't. It seems like the most flexible solution, and therefore the best. However, keep in mind the adage <a href="http://en.wikipedia.org/wiki/Tanstaafl" rel="nofollow noreferrer">TANSTAAFL</a>. Here are some of the disadvantages of EAV:</p> <ul> <li>No way to make a column mandatory (equivalent of <code>NOT NULL</code>).</li> <li>No way to use SQL data types to validate entries.</li> <li>No way to ensure that attribute names are spelled consistently.</li> <li>No way to put a foreign key on the values of any given attribute, e.g. for a lookup table.</li> <li>Fetching results in a conventional tabular layout is complex and expensive, because to get attributes from multiple rows you need to do <code>JOIN</code> for each attribute.</li> </ul> <p>The degree of flexibility EAV gives you requires sacrifices in other areas, probably making your code as complex (or worse) than it would have been to solve the original problem in a more conventional way.</p> <p>And in most cases, it's an unnecessary to have that degree of flexibility. In the OP's question about product types, it's much simpler to create a table per product type for product-specific attributes, so you have some consistent structure enforced at least for entries of the same product type.</p> <p>I'd use EAV only if <em>every row</em> must be permitted to potentially have a distinct set of attributes. When you have a finite set of product types, EAV is overkill. Class Table Inheritance would be my first choice.</p> </blockquote>
    singulars
    1. This table or related slice is empty.
    1. POWhat are the [dis]advantages of using a key/value table over nullable columns or separate tables?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USConrad Frix
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhat are the [dis]advantages of using a key/value table over nullable columns or separate tables?
      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. VTAcceptedByOriginator
    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. VTUpMod
    1. COEvery idea I had is covered in that answer. Heh! Thanks.
      singulars
      1. PO
      1. USTaudris
    2. COThe 5 "no way" sttmts are categorically incorrect: they may well be true about poorly implemented EAVs, but they do not reflect EAV implemented porperly. There is no problem at all implementing all of them in EAV, in SQL. Performance is in fact faster on 6NF (EAV is a subset of 6NF) than 3NF or 5NF, so there again, the statement is not about EAV. SQL is certainly more complex, but that is easily overcome by metadat, and automating SQL production. Tabular layout is simple to provide via VIEWS. Agree that it is not for everyone, and done badly, it stinks.
      singulars
      1. PO
      1. USPerformanceDBA
    3. CO@PerformanceDBA. You may want to post your own answer. I'm really interested in any evidence you have on performance. I'm also interested in what "automating SQL production" means. Does this mean generating tables? How does this differ than automating the deployment one of the other choices?
      singulars
      1. PO
      1. USConrad Frix
 

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