Clarification please on a prior post.

Frunkie · #1 (**permalink**) 06-04-07, 02:45

I am looking for clairification on a post I read here:
One field refers to fk field in more than one fk table

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

pootle flump · #2 (**permalink**) 06-04-07, 04:07

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

Frunkie · #3 (**permalink**) 06-04-07, 04:41

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!

pootle flump · #4 (**permalink**) 06-04-07, 04:43

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.

Frunkie · #5 (**permalink**) 06-04-07, 04:59

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.

pootle flump · #6 (**permalink**) 06-04-07, 05:05

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.

Frunkie · #7 (**permalink**) 06-04-07, 05:19

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.

Frunkie · #8 (**permalink**) 06-04-07, 05:32

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech