• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JPA mapping/ERD advice

 
Brian Smith
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a new data model that I am creating to link to an existing legacy database. The legacy system contains address and geographical information.

The new system is a Service Request system that needs to tie into the address data from the legacy system.

The users have asked to be able to look up and enter service requests via city landmarks and also intersections.

So I have a table (Address) that is from the legacy system (it has compound keys, yuck I know)

Address Table
HouseNum - pk
HouseSuffix - pk
Street - pk
CityBlock - pk
ParcelNumber - pk

Now I have a Landmark table and Intersection table that both need to relate to the address table via Many to One (since the same relationship exists, I will use Landmark for the example)

Landmark Table
LandmarkID - pk
LandmarkName
{keys from Address} - fk

The Service Request table must relate to the Address table (M-1) in order to get all of the geo spacial data and validate addresses against the legacy system. The users can either enter an address directly, choose a landmark (which relates to an address), or choose an intersection (which relates to an address), but never have both an intersection and an address.

ServiceRequest Table
RequestID - pk
{keys from Address} - fk
AddressTypeID - fk (indicates "landmark", "intersection", "address", etc)

The problem comes in when I need to know which landmark was chosen when entering the ServiceRequest. Based on the AddressTypeID I will know whether a landmark was chosen but not necessarily which one. I really don't like the idea of storing the LandmarkID on the ServiceRequest table since I would also need fields for intersection and whatever other future ways to categorize the address and it would create a nice big circular relationship between Landmark, Address, and ServiceRequest.

So to recap here is the structure

Address 1<---->M ServiceRequest
Address 1<---->M Landmark
Address 1<---->M Intersection

A Service Request will either have an address, landmark, or an intersection or neither (never more than one), how do I avoid having LandmarkID and IntersectionID stored directly on ServiceRequest?

Is there any other way I can design this so I will know which landmark was entered when viewing the ServiceRequest? The only table I don't have control over is Address.
[ June 28, 2007: Message edited by: Brian Smith ]
 
Mike Keith
author
Ranch Hand
Posts: 304
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, if it were me I would make an inheritance hierarchy out of Landmark and Intersection. Create a superclass entity (e.g. AddressReference) and put the common stuff (like the Address relationship, etc) into it and make Landmark and Intersection subclasses. A SINGLE_TABLE mapping approach would work best, although if you plan on adding more subtypes then a JOINED one would work too. Then you have a ServiceRequest that has a m-1 relationship to AddressReference.
 
Brian Smith
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply Mike. I tried that approach initially, (except TABLE_PER_CLASS) The problem I could not get past was the fact that a user can enter the address directly into ServiceRequest as an alternative to selecting a landmark or intersection. So I needed the Address Keys on ServiceRequest as well. So I was maintaining the Address on ServiceRequest as well as on the Landmark/Address relationship. It just seemed ugly to me. The other problem is that an Address can have many landmarks/intersections associated with it. So I would have a 1-M relationship between Address and "AddressReference". My end goal is to be able to view all ServiceRequests linked to an address regardless of how the user entered the address (via address, landmark, or intersection). So I need a way to maintain a 1-M between ServiceRequest and Address as well be able to get to the specific Landmark/Intersection that was entered when viewing the ServiceRequest.

Thanks again
 
Mike Keith
author
Ranch Hand
Posts: 304
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you don't want to store the address FK in the SvcReq then you can make your inheritance hierarchy have an additional subclass called SimpleAddress, a sibling of Landmark and Intersection. It will have its own simple id (like the other two subclasses), and the instance can be stored in the SvcReq as either one of the three types (address, intersection or landmark). Note, though, that if you don't want to store the address fk in SvcReq, as you showed in your first post, then the downside is that to get the address you need to traverse a second relationship, just as you would if you were following a landmark or intersection. May not be a big deal, though, depending upon your application.

Good luck.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic