Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2010
    Posts
    57

    Comments appreciated on my design (event tracking database)

    I appreciate any comments on the design I'm implementing. Thanks to R937 for setting me on the path to types/subtypes

    Here is where I'm at, at the moment.

    I have a table of vehicles. Each vehicle can have a number of events associated with it (transportation, delivery, maintenance, failure, etc) The relationship between these is easy:

    (PK and FK used for ease of writing, I don't actually use those names as my columns)
    Vehicle.PK = Event.FK

    All events have some simple information associated with them like a date and description. However, depending on the event type, there might be additional information specific to that type. So I've set up additional tables event_maintenance, event_delivery, etc.

    I set up the tables such that:
    Event.PK = Event_SUBTYPE.FK (for each subtype table)

    The problem is that now I can have multiple subtype rows associated with one event, which doesn't make sense. I can set a one-to-one constraint, but I only know how to do that across one pair of table.

    The other catch is that for one particular kind of event (delivery) one of the subtype rows could be associated with multiple events (i.e. multiple vehicles in the same delivery)

    As I said, I appreciate any suggestions on how to set this up.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by carlosn View Post
    So I've set up additional tables event_maintenance, event_delivery, etc.

    I set up the tables such that:
    Event.PK = Event_SUBTYPE.FK (for each subtype table)

    The problem is that now I can have multiple subtype rows associated with one event, which doesn't make sense.
    well, it actually does, although i think i can see the confusion

    an event can have any event subtype, and in practice, your application logic would ensure that a subtype row is created only in the correct subtype table and none other (it wouldn't make sense for an event which is a delivery to have rows in both the delivery subtype table and the maintenance subtype table)

    there are ways that you can enforce this in the database but they are a bit complex, and if you are okay with letting application logic do it, then i am too

    as an aside, consider if you didn't have subtype tables at all, but had one master table with nullable fields for all the different subtype columns... do you normally have application logic to ensure that only the correct set of subtype columns and no others are populated by the application? because that's ~really~ hard to do in the database

    anyhow, the other side of this question is retrieving data from subtype tables, and that's either done in two queries (get the event, find out what type it is, and query the appropriate subtype table) or one query with as many LEFT OUTER JOINs as there are subtypes

    incidentally, what you get back from the single query with all the LEFT OUTER JOINs is a record set that looks exactly like the single master table with all the nullable columns

    helps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2010
    Posts
    57
    Thanks for your quick and helpful reply. That actually makes sense. In my drive toward a wonderful database design, I'd forgotten there would be a front end that could enforce certain logic rules. Seems like this might be the best approach for now.

  4. #4
    Join Date
    Aug 2010
    Posts
    57
    These two links also had some useful ideas on how to implement what I wanted to accomplish:
    Data Based : Distributed Keys and Disjoint Subtypes
    and
    Implementing Table Inheritance in SQL Server - SQLTeam.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    both of those are excellent articles, and both deal with what i mentioned earlier -- "there are ways that you can enforce this in the database but they are a bit complex"

    if you understand what they are suggesting, then you've got a good handle on this particular data model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •