Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Clarification please on a prior post.

    I am looking for clairification on a post I read here:
    http://www.dbforums.com/showthread.php?t=1618800&page=2

    What I am trying to understand is quoted below where Pat does not recommend adding an inventory type to store a value in a master table, that identifies the item.
    Quote Originally Posted by jfugiel
    You could certainly add a inv_type field and store the value of 'server' or whatever to identify the individual inventory item. However, this still doesn't tell anyone (dba's or programmers) what values it is legal to put where. If you have a field called raid_configuration that should only contain a value if inv_type = 'server'... how would anyone else know that?,
    Quote Originally Posted by pat phelan
    While I would emphatically NOT recommend it, I have seen positively Machiavellian webs of constraints used to do exactly what you've described (if attribute A is blank, then attribute B must have a value; if attribute J has the value "S", then attribute Q must have a value in {1, 2, 5, 7} and so forth). I prefer to use entity decomposition (using a master table to store shared attributes and separate tables to store attributes specific to a sub-class) because in my mind that is a cleaner and simpler way to do things.
    Can someone tell me in layman terms what Pat is getting at please?

    I am understanding what "jfugiel" is describing to be what I call a lookup table or lookup field that is specific to only that table. In the case of a parent table:

    Code:
    lookup_table
    Id
    permissable_field1
    permissable_field2
    permissable_field3
    
    employee_table
    employee_Id
    Id
    field1
    field2
    I actually have a pic I have included of one of these lookup tables.

    I like to use these types of "lookup" tables for RI purposes. Sometime I have used a lookup field instaed as jfugeil stated. Is this what Pat is saying he would not recommend? The parent table or the field or both? Why is this not a good idea?

    Sorry for my ignorance, I'm just trying to learn. Thanks

    Frank
    Attached Thumbnails Attached Thumbnails export.jpg  

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What Pat is talking about is having a single "master" table to store common fields and then a further n tables to store non-common fields. Not much more sense huh?

    Imagine you have a database for storing information about a sports club. You want to store info on players and coaches. They are both people but you are also interested in different things about them.

    You could create a master table for all the attributes they have in common (name, gender, address... the usual bio details) and then a players table for the player specific information (position played, squad number etc) and a table for coaching staff information (job title, occupational specialty etc.).

    Advantages\ features:
    Less nulls stored (though people may argue about whether or not this is a feature or advantage)
    People can be players, coaches or player\ coaches
    No need for complicated check constraints if a single table is used ensuring that (for example) squad number is only completed for players.
    Whether a person is a player or coach can be inferred from presence\ absence of a record in the player & coaching tables.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Thanks Pootle, what was really throwing me was the relational algebra. I couldn't see where Pat was going with that.

    I have used master tables before and I personally like them. I think it is a much cleaner design indeed.

    Anyway, thanks for the clarification!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by fjm1967
    Thanks Pootle, what was really throwing me was the relational algebra. I couldn't see where Pat was going with that.
    You were not the only one I'm still not really much the wiser.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    I swear, I've read that post 6 times now and I'm still scratching my head. I mean, I understand completely what jfugiel wanted to accomplish in his first post but then a few posts down it just downhill for me.

    GUIDs and relational algebra. Wow.

    Much of the terminology I still have to look up or google because I am not familer with it. But, little by little I'm getting there.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by fjm1967
    GUIDs and relational algebra. Wow.

    Much of the terminology I still have to look up or google because I am not familer with it. But, little by little I'm getting there.
    RA is a discipline in itself. GUIDs are pretty elementary.

    As I mentioned - I studied RA as part of a post grad course but I think Pat is an exception to the norm if he uses RA in his work. I certainly have never touched it since and would really struggle to use it now.

    Having said that, I expected more algebra - his subsequent post suggested more that he used relational terms which isn't the same thing to my poor ignorant eyes.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by pootle flump
    I certainly have never touched it since and would really struggle to use it now.
    You know, I really struggle with modeling sometimes and man.. I'll tell you that the concept for me was really tough and still is for the most part, but it is getting easier the more I do it. I couldn't imagine using RA or even learning it for that matter. I never really liked algebra that much in school but I find myself using it more these days.
    Quote Originally Posted by pootle flump
    Having said that, I expected more algebra - his subsequent post suggested more that he used relational terms which isn't the same thing to my poor ignorant eyes.
    Me too. When you asked me yesterday if I had read that post, I laughed outloud because I was following it from day 1.

    I know Rudy really wanted to see the RA from Pat as well.

  8. #8
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    How would you model vehicle, year, make and model in 3NF?

    Hey, look what I found! http://cheng.cc.cycu.edu.tw/DBMS/con...ap7/sld045.htm

    Rudy should appriciate this.
    Last edited by Frunkie; 06-04-07 at 06:43.

Posting Permissions

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