Databases
StackOverflow2013
Postsdbo
POSQL Design, Joining Types and Subtypes
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Design, Joining Types and Subtypes
    primarykey
    data
    text
    <p>I'm making a traffic logging front end which allows displaying information on a specific vehicle on property as well as searching for a specific vehicle, but am unsure of the <em>best</em> way to proceed with my database design. What I want to do is be able to quickly pull up all vehicle records based on the type of vehicle and features/subtypes of that vehicle. I don't know how best to make reference from my single vehicle record to multiple subtypes/features. Here's a simplified example:</p> <p>I have a table for Vehicles:</p> <pre><code>VehicleID, LicensePlate, TypeID, SubTypeIDs 1 , 111111 , 2 , ;2;;3; </code></pre> <p>A table for VehicleTypes:</p> <pre><code>TypeID, Type 1 , Car 2 , Semi </code></pre> <p>And a table for VehicleSubTypes:</p> <pre><code>SubTypeID, TypeID, SubType 1 , 1 , Coupe 2 , 2 , Flat Bed 2 , 2 , Sleeper </code></pre> <p>The Vehicles.Subtype field is a varchar, in which I'm currently just deleniating out the Subtypes which reference to VehicleSubTypes.SubTypeID... The idea is to, from the front end, pick out each SubTypeID when listing available options and look up the referenced SubType string (ie, "Coupe") to display to the user, or more importantly when searching for a Semi with a sleeper and a flat bed to include a SubTypes LIKE '%;2;%' AND '%;3;%' clause to get only vehicles that include both features. I'm only thinking of this solution though right now because I've been on vacation for a year and my brain is stalling out on me :) I feel certain this is awful DB design! But for the life of me I can't think of a more proper way, and all my googling efforts keep turning up subtype examples that simply don't apply or I'm missing the similarity with (ie, people with multiple sets of contact information.. people should obviously be one table, contact info should obviously be another, they link by personID, etc etc)</p> <p><strong>EDIT/Conclusion:</strong></p> <p>Thanks to Bort for jarring my memory and pointing me toward a link table. I've now added a table, Link_VehicleToSubTypes:</p> <pre><code>linkID, VehicleID, SubTypeID 1 , 1 , 2 2 , 2 , 10 //10 = Cargo (Semi) 3 , 2 , 15 //15 = No Sleeper </code></pre> <p>In addition, I've created the following stored procedure to return VehicleIDs for vehicles that match all parameters (up to 10) that I've passed to it - this way I can later join this info against results from searching my Vehicles table which includes vehicle-specific info such as Vehicle.Color, and thus I can filter a final result set:</p> <pre><code>ALTER PROCEDURE dbo.ReturnVehicleIDsMatchingSubTypes ( @SubType1 int = NULL, @SubType2 int = NULL, @SubType3 int = NULL, @SubType4 int = NULL, @SubType5 int = NULL, @SubType6 int = NULL, @SubType7 int = NULL, @SubType8 int = NULL, @SubType9 int = NULL, @SubType10 int = NULL ) AS DECLARE @intNumberSubTypesToMatch int SET @intNumberSubTypesToMatch = (SELECT COUNT(@SubType1) + COUNT(@SubType2) + COUNT(@SubType3) + COUNT(@SubType4) + COUNT(@SubType5) + COUNT(@SubType6) + COUNT(@SubType7) + COUNT(@SubType8) + COUNT(@SubType9) + COUNT(@SubType10)) SELECT VehicleID FROM Link_VehicleToSubTypes WHERE SubTypeID IN (@SubType1, @SubType2, @SubType3, @SubType4, @SubType5, @SubType6, @SubType7, @SubType8, @SubType9, @SubType10) GROUP BY VehicleID HAVING (COUNT(*) = @intNumberSubTypesToMatch) RETURN </code></pre> <p>I've tested this out and it's working well. My implementation of the stored procedure may be a bit wonky (I've never had to count non null parameters before, this method is all that came to mind), but it works. Bort - I'll +1 you when I have enough points to do so! Your help is greatly appreciated!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmaxx233
    1. USmaxx233
    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. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Design, Joining Types and Subtypes
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Design, Joining Types and Subtypes
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 because you feel certain that is awful design. Indeed it is (the `SubTypeIds` column :)
      singulars
      1. POSQL Design, Joining Types and Subtypes
      1. USypercubeᵀᴹ
    2. COSee the Vehicles example in this article: [Unique Experiences! by Joe Celko](http://www.simple-talk.com/sql/t-sql-programming/unique-experiences!/) (he expands on this in several newsgroup threads e.g. [here](http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=158761)).
      singulars
      1. POSQL Design, Joining Types and Subtypes
      1. USonedaywhen
 

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