Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Your proposed property-based approach is reasonable, especially if there is a need to create new contact types and properties at run-time without changing the schema.</p> <p>If the set of contact types and properties is fixed at run-time, then you might alternatively consider creating a subsidiary table for each type. That is, create tables like <em>StudentInfo</em>, <em>StaffInfo</em>, etc. If you did this, you would no longer need the <em>ContactType</em> field as it would be implicit in the presence of a row in the corresponding subsidiary table. This model would also handle awkward situations where someone falls into two categories, e.g. a student who is also staff.</p> <p>Ignoring all that, however, let's look at your second question about querying the property tables. You can perform such queries. Your example would be answered by this query:</p> <pre><code>SELECT * FROM Contact AS c INNER JOIN ContactMetaData AS crs ON crs.ContactId = c.ContactId AND crs.PropName = 'CourseName' AND crs.PropData = 'IT' INNER JOIN ContactMetaData AS av ON av.ContactId = c.ContactId AND av.PropName = 'Average' AND av.PropData = 10 WHERE c.Forename LIKE 'D%' </code></pre> <p>The trick is to join to the <em>ContactMetaData</em> table multiple times, once for each custom property you wish to test.</p> <p>A problem with this style of query is that it will almost certainly have to be generated at run-time -- after all, the set of properties is dynamic at run-time. You can avoid this by taking a different approach to expressing the query:</p> <pre><code>DECLARE @propertyCriteria TABLE ( PropName NVARCHAR(200) NOT NULL, PropData NVARCHAR(200) NULL ) INSERT INTO @propertyCriteria VALUES ('CourseName', 'IT') INSERT INTO @propertyCriteria VALUES ('Average', '10') SELECT * FROM Contact AS c WHERE c.Forename LIKE 'D%' AND NOT EXISTS ( SELECT * FROM @propertyCriteria AS crit LEFT JOIN ContactMetaData AS meta ON meta.ContactId = c.ContactId AND meta.PropName = crit.PropName AND meta.PropData = crit.PropData WHERE meta.ContactId IS NULL ) </code></pre> <p>This has the advantage that the query is now fixed at run-time as the dynamic property criteria are determined by what is inserted into the temporary table <em>@propertyCriteria</em>. The disadvantages are that there is now a need for the temporary table, and that the query will not perform as well as it did with inner joins (although you probably won't notice the difference if your database only has 50,000 records in it). Also note that this second approach only works for criteria which are ANDed together. If you want ORs, the solution becomes even more complex.</p> <p>Incidentally, if you are going to allow NULLs in both the property values and the criteria, don't forget to account for the fact that comparison operators involving NULLs always return false (i.e. NULL = NULL is false, NULL &lt;> NULL is false, etc).</p>
    singulars
    1. This table or related slice is empty.
    1. POStoring Searching Records Based on Multiple Conditions
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USWReach
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

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