July 24, 2009 at 12:22 pm
Scott,
Glad to be of assistance. I've never had a situation like that before and it was good to figure it out. Good luck with your project.
Steve,
Thanks for your comments. I was glad to see that your code was very similar to mine. It helped to confirm that I wasn't totally off track with my thinking.
Jason
July 24, 2009 at 1:21 pm
K guys,
Since you seemed to enjoy the challenge I wonder if I can impose one last question on you.
An extension of the existing problem domain.
Consider two new tables:
Landlord
Apartment
Landlord has only one City. City has many Landlords.
Landlord has 0, one ore more Apartments. Apartment has zero or 1 Landlord.
Apartment has only one Area. Area has many Apartments.
As before, Area has One City.
What I've done, is simply added an AreaID and LandlordID to the Apartment table. This works great. LandlordID is nullable, while AreaID is NOT. I noticed another problem though...how can I prevent the following update anomolies? Say Apartment "A" is in Area "Brooklyn" (which is in City "New York"). If Apartment "A" has no Landlord, there is no problem. But what if Apartment "A" has Landlord "L" which is in City "New Jersey". The Apartment has an Area "Brooklyn" which is in the City "New York", but it belongs to a Landlord "L" which is in a city "New Jersey". This is again, a violation of business rules. I think using composite foreign keys as before I can constrain this. I'm attempting to do so now, but thought I'd throw it out there in case the answer is a simple enough one for one of you pros 😉
Thanks again,
Scott.
July 24, 2009 at 1:34 pm
I guess in addition to the AreaCodeID, I can add a CityCodeID to the Apartment table. Then I can constrain Apartment and Area by AreaCodeID and CityCodeID, and Constrain Apartment and Landlord by LandlordID and CityCodeID.
The only reason this gives me pause is that it causes so much redundancy in my schema.
So for example, every Area has exactly one city. So, it seems unnecessary to put AreaID AND CityID in the Apartment table, because you can get to City By joining to Area.
But I think it might be a necessary evil to accept if I want to eliminate the paradox described in the previous post...
July 24, 2009 at 1:55 pm
The only problem I can foresee here is that what you describe so well as business rules definitely does not correspond to the real world, as landlords often have multiple addresses and it's often impractical (again, in the real world) to ever do less than assign a landlord to specific addresses, and make those addresses available to application users whenever they deal with that landlords data.
The reason I'm going in this direction is that your scenario is starting to resemble the proverbial Rube Goldberg device, and thus I have to start asking the question as to EXACTLY what real-world scenario are you trying to get a model for? Analogies to common things are often useful, but the moment they deviate from the real-world scenario being modelled, the usefulness tends to disappear, and continuing with the ever more complex machinations of the design needed starts to become an exercise so futile that one could far better spend their time if they just know up front the ENTIRE scenario, and it's genuine real-world data items needed.
That's not to say you might not need exactly what you're asking for... but it's just starting to sound like a complex homework problem instead of a real-world scenario...
Can you elaborate please?
Steve
(aka smunson)
:-):-):-)
Scott Klarenbach (7/24/2009)
K guys,Since you seemed to enjoy the challenge I wonder if I can impose one last question on you.
An extension of the existing problem domain.
Consider two new tables:
Landlord
Apartment
Landlord has only one City. City has many Landlords.
Landlord has 0, one ore more Apartments. Apartment has zero or 1 Landlord.
Apartment has only one Area. Area has many Apartments.
As before, Area has One City.
What I've done, is simply added an AreaID and LandlordID to the Apartment table. This works great. LandlordID is nullable, while AreaID is NOT. I noticed another problem though...how can I prevent the following update anomolies? Say Apartment "A" is in Area "Brooklyn" (which is in City "New York"). If Apartment "A" has no Landlord, there is no problem. But what if Apartment "A" has Landlord "L" which is in City "New Jersey". The Apartment has an Area "Brooklyn" which is in the City "New York", but it belongs to a Landlord "L" which is in a city "New Jersey". This is again, a violation of business rules. I think using composite foreign keys as before I can constrain this. I'm attempting to do so now, but thought I'd throw it out there in case the answer is a simple enough one for one of you pros 😉
Thanks again,
Scott.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 24, 2009 at 2:24 pm
Scott,
I think that you could gt it to work as a modification of what we've done before, but, assuming that this isn't a homework problem, I'm thinking that it would be easier to handle through stored procedures or something. I usually don't like to model business rules in the table structure like we've done. If the business rule changes, you'll end up having to modify tables and their relationships. If you use a stored procedure update landlord assignments to an apartment, then you have control over preventing an incorrect assignment and it's easier to modify if the rule changes.
Jason
July 24, 2009 at 3:32 pm
Hi Steve,
The real world problem is exactly as I've outlined it. I'm modelling Apartment and Landlord data the way I've described.
I think you and Jason may be right in that I'm just being a bit OCD here and there is no such thing as a perfect model. Even if I did achieve this maybe the tradeoff of complexity is to great to be justified, not sure.
sk
July 24, 2009 at 3:57 pm
I think you're right about the OCD. I've been known to do that from time to time, and in this case, I doubt it's necessary. What kind of user base are we talking about, or is this for your own consumption, and from what perspective are you tracking data (e.g., is it used for web-based search, used for tracking specific properties as a service to landlords, etc., etc.)? All of those things play into how best to put it together, and even then, there are probably a number of ways to handle it.
If you're looking at feeding a web-based search on Area, it might be easier to adopt a slightly more encompassing heirarchy ... something along the lines of :
LOCATIONS
-- STATE (NY, NJ, CT, etc.)
-- CITY (NYC, Newark, Parsippany, Greenwich, etc.)
-- AREA (Bronx, Queens, Manhattan, Long Island, etc.)
PROPERTIES
-- AREA_ID
-- ADDRESS
-- LANDLORD_ID
-- PROPERTY CONTACT INFO
-- DESCRIPTION
LANDLORDS
-- LANDLORD_ID
-- NAME
-- LANDLORD CONTACT INFO
That's a real rough outline, but I'm sure you get the idea. Let me know what your ultimate objective is, as it's tough to guess those details.
Steve
(aka smunson)
:-):-):-)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply