for maintaining addresses (and other entity attributes) via an ENTITY table. I got some good feedback, but I would like more.
Option 1
Option 2
In short, I prefer Option 1 as it requires no further modifications (if adding another entity) and you won't have to write funky SQL to get all the addresses (though you would have to UNION the people and organizations table and any subsequent entity). The one bad thing perhaps is that you would have to keep a close eye on your code to prevent someone from subverting your process...I think anyway.
So please vote and sound off in comments as to the "Why" of your choice.
(If you choose neither, please, please, please explain!)
11 comments:
I like option 2. On the addressid table, I would probably do it as an IOT.
Chet,
You skipped my suggestion ;) I would just add a few columns to both tables to hold the information.
Regards,
Dan
I didn't forget, I'm just trying to avoid putting the addresses inline with the "entity." That way we can standardize how it's done. If we have multiple entities (organizations, people, etc), there would be multiple entry points for address information making it harder to standardize.
I'm all for denormalization in certain circumstances, but I don't think this is one.
Option 2 is acceptable. Option 1 is a very ripe opportunity for data corruption requiring the developer to have intimate knowledge of the physical model to prevent corruption.
@cristaunity
So you would be willing to add a column each and every time you added a new "entity?"
I know it's not that big-a-deal, but I have felt that the ENTITY table makes it possible to avoid adding new columns for each new "entity."
Maybe I'm misunderstanding the model. If the addresses table in option 2 is not representing an intersection of people and organizations, then it also not acceptable (with the FK* columns in addresses as null optional? Yuck)
My style of denormalization in such a technique is different than either option. The core difference being the highly selective elimination of a physical FK in the equation.
In other words, my setup would be:
people
- PK peopleid
organization
- PK organizationid
addresses
- PK addressid
- parentid
Where peopleid, organizationid and parentid are the same data type.
Where parentid is a logical "arc" foreign key but is not physically enforced.
In previous lives, I have used a triggers to enforce data integrity with a procedural means in place of a declarative means, but this proved not worth the effort for a variety of reasons.
@cristaunity
Love your writing by the way. I wish I had the same skills.
Wouldn't that parentid be roughly the same as the entityid in Option 1? It looks like it to me...
Thank you for the compliment. I hope you find a new gig soon; this blog writing doesn't pay well enough. If you want to relocate to walking distance to the sun, consider the Dallas market - things are still booming here for Oracle nerds.
Back to this very good practical discussion...I suppose my technique is essentially the same as option #1, now that I've uncorked my brain and understand the original question.
I think if you were ok with option #1 you'd be ok with my suggestion, or vice versa.
And of course (reversing my previous post) option #2 is an awful stinker.
My Spidey Senses tingle when I consider the foreign keys in option #1's people and organizations. They are sort of Bizarro World foreign keys.
Option #1 does have data integrity going for it, but that integrity doesn't enforce the actual model you want to express, as entity is the intersection table not the parent table in the equation.
If I were coping with physical models where homogenous primary key data types existed I would continue down my path. It would be physically more efficient and not introduce a FKs that didn't pass my sniff test.
I have a Java friend (gasp) who has a similar style. I keep trying to get him to start...he's absolutely hilarious.
I've made a whopping $20 in almost a year of blogging! I'm still trying to figure out why I keep the ads there...
Walking distance to the sun...very appropriate for Dallas. I used to play in baseball tournaments there, coming from Denver...needless to say, it wasn't fun.
Too entrenched here, for now anyway. Parents help take care of the kids so I can talk to my wife once in awhile.
I guess Option 1 is similar to the Super Type or Base Type (???) in OO programming. Perhaps base class that is extended via PEOPLE and ORGANIZATIONS?
The FKs in PEOPLE and ORGANIZATION are also their PKs which allow you to directly access ADDRESS (is that what you meant by arc?).
I was never very good at word problems...do you a pretty picture for the challenged? ;)
(and I do think that Option 1 and yours were similar...i think)
chet
@tom
I have no idea what IOTs are!
Seriously though...I don't have enough information about IOTs to agree or refute you. It's on my list of things to look into.
might want to give Martin Fowler's Party pattern a read.
Post a Comment