Databases
StackOverflow2013
Postsdbo
POStoring Searching Records Based on Multiple Conditions
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStoring Searching Records Based on Multiple Conditions
    text
    copied!<p>I have a table of people quite standard stuff e.g. :</p> <pre><code>CREATE TABLE [Contact]( [ContactID] [bigint] IDENTITY(1,1) NOT NULL, [ContactType] [nvarchar](50) NULL, [Forename] [nvarchar](60) NULL, [Surname] [nvarchar](60) NULL, [Company] [nvarchar](60) NULL } Example Data : 01, "Student", "Bob", "Smith", Blank 02, "Staff", "Robert", "Smithe", "Roberts And Sons" Etc </code></pre> <p>This table contains all the fields common to all contacts. However i have some "types" of contact that may or may not have a field which is specic only to that type of contact. For example if the record has "ContactType='student'" i want to store an extra field called "studentid". There are many different types of contact each with slightly different field requirements. To add to this situation at somepoint in the future each contact type might have extra fields added.</p> <p>If i add each field to the contacts table i would end up with lots of fields which are not required for 99% of the records. So i was planning on creating a second table like this:</p> <pre><code>CREATE TABLE [ContactMetaData]( [ContactID] [bigint] NOT NULL, [PropName] [nvarchar](200) NOT NULL, [PropData] [nvarchar](200) NULL ) Example Data: 01, "StudentID", "0123456" 01, "CourseName", "IT" 01, "Average", "10" 02, "Ranking", "22" 02, "ProductTypes", "IT Equipment" ETC </code></pre> <p>For each extra field i just add a record into this table with the name and value for the field. I can use code to pull this information up etc.</p> <p>My question is</p> <p>Is this the best approach as i'm stumped of another way other than a huge table with every single field. Given this approach is it possible to do complex querys across many of the property fields and if so how? e.g. how would i list all the students on the "IT" course with an "Average" of 10 whos "forename" starts with "D"?</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