1:1 relationship in database (simple...) 2

[dragonwell]

(if there is a "Database-Design/Modeling" forurm, please excuse this, but this seems like the best place)

In my database I have two tables which have a 1:1 relationship, we will call Person and Address. If Person "has" an Address, I would think that the Address table would have a foreign key of the PersonID. BUT, I have another entity, say "Company" which also has addresses. So now Address does not necessarily belong to a Person.

So now should I have a relationship table which contains the PersonID and the AddressID, to show which address the Person has, as well as a CompanyAddress table to show which address is owned by the Company?

Also, Company will have more than one type of address (MailingAddress, PhysicalAddress, etc.)... So for each different type of address it has - a different "glue" table exists (CompanyMailingAddress, CompanyContactAddress, etc.)

Does this sound correct or is there a better way?

Greetings,
Dragonwell

 

[dragonwell]

Sorry, I just found the "GEneral Database Discussion" forum.
trying over there...

Greetings,
Dragonwell

 

[jfrost10]

You may have found an answer in the database discussion forum, but I'll weigh in here anyway.

We've done similar things in our apps (with things like Addresses being used for companies and customers), and we've found it much easier to just split them out:

Customer-->CustomerAddress
Company-->CompanyAddress

if you try and put all your eggs in one basket, then you end up with crazy type-identifiers for what type of address it is (customer or company), and then further if each type has further types (like the Physical Address, Mailing address, etc. that you mentioned).

You could still have a base Address table, which stored all the base address information, then had sub-tables which linked with the base Address table and contained specific information for the type of address...but that just gets messy.

Splitting it off into seperate tables might be an easier way to handle it.

hth

D'Arcy

 

[dragonwell]

Thanks! You hit it right on the head, as usual.



Greetings,
Dragonwell

 

[stevexff]

Addresses are rarely that simple. People have billing addresses, delivery addresses, risk addresses, they want their mistress's credit card bill sent to their office not their home address, etc. etc. I would recommend having a 'pure' address table. No names, no address types, just addresses. And a dumb key. You will probably need an index on the postal code, too.

Then you can link people, companies, customers, or accounts to addresses, using another table (or tables) as the link. This has the advantage that multiple things can share the same address, and things can use different addresses for different purposes. By adding a 'start date' and a nullable 'end date' on the linking tables, you can even keep an address history, e.g.

[b]Customer[/b]
cust_id
name
...

[b]Address[/b]
addr_id
number
street
town
postcode
...

[b]address usage[/b]
cust_id
addr_id
usage_type
start_date
end_date
...

So printing out a letter to a customer means joining the customer table, the address usage table, and the address table, and using the current date as a predicate to make sure you get the current one.

 

[jfrost10]

so...then if you were to use the same addresses for customers, accounts, and companies as well, you'd need a structure like this:

address usage
Cust_ID
Comp_ID
Acct_ID
Addr_ID
Usage_Type
Start_Date
End_Date

Or, as you mentioned, you'd need to have an "Address Usage" table for each scenario (Customer Address Usage, Company Address Usage, etc.)

It just seems like alot of work to me to be able to reuse an address.

I still think doing a 1:1 relationship between addresses and entities like customers, companies, etc is the better way to go, and less of a headache to maintain/manage.

D'Arcy

 

[dragonwell]

Here's what I did.

[b]Person[/b]
PersonID
Name, etc

[b]Company[/b]
CompanyID
Name, etc

[b]Address[/b]
AddressID
Ln1
Ln2
City
Region
etc

[b]PersonAddress[/b]
PersonAddressID
PersonID
AddressID

[b]CompanyPublicAddress[/b]
CompanyPublicAddressID
CompanyID
AddressID

[b]CompanyPrivateAddress[/b]
CompanyPrivateAddressID
CompanyID
AddressID

Sounds crazy. Probably wrong. Working for now....



Greetings,
Dragonwell

 

[wgcs]

Here's a way that was briefly (but negatively) commented on above:

[b]Person[/b]
PersonID
Name, etc

[b]Company[/b]
CompanyID
Name, etc

[b]Address[/b]
AddressID
Ln1
Ln2
City
Region
etc

[b]AddressGlue[/b]
AddressGlueID
AddressType -- arbitrary type id, optional based on function: if ForeignTable='Company', this would be 'PUBLIC' or 'PRIVATE', if ForeignTable='Person', this could be ignored.
Description -- particularly for when a user can enter several addresses and the system doesn't need to distinguish  between how each is used, this is a user-entered description.
ForeignTable
ForeignKeyID
AddressID

 

[jfrost10]

The AddressGlue doesn't stick.

You're ForeignKeyID wouldn't be a true FK, as it would need to store ID's from both the Company and Person table; you could have people and companies with the same ID, so that field wouldn't be unique.

And even if you kept it as just a numeric field that allowed duplicates, you're now going to have to hard code table names in the SP for saving the data:

If @Table = 'Company' Then...

And for every other entity you create, you'll have to modify the SP's...or the table that contains a listing of the different tables that can be associated with an address (using the numeric ID of the table instead)...which means that in your presentation or logic code of the app, it needs to ensure it uses those values when passing to the database...which means ensuring all of this is brought to all levels of development.

OR...

you can do what Dradonwell did, which I think is the best solution for the scenario if you're going to go with only one address table (the only thing I may have done differently is just set a bit field in the company address table for 'IsPrivate' instead of splitting off into two seperate tables...but what he did works).

Actually...question to Dragonwell: in almost every app I've seen, the address is actually part of the entity (customer, company, etc.)...are you guys just trying to make you're system uber-robust in that it can handle multiple addresses? Or maybe your requirements call for it...I'm just curious what the overall sceanrio is.

D

 

[stevexff]

jfrost10

A lot of systems don't even go this far. Some just store the customer name and address on the 'product' entity - e.g. insurance policy, mortgage or similar. This is a bad idea as it leads to duplicated customer information. Since dragonwell was going to the trouble to split the customers and addresses, I assumed it was for flexibility not just performance reasons.

Also, it depends if you are looking at an entity with your DBA head or your OO design head. An account entity can have a 'getStatementAddress()' method and this could have a behaviour like

1. attempt to get the statement address for this account, if there is one.
2. If not, identify the owning customer, and return his address instead.

But like all design, it's a tradeoff between complexity and flexibility, and ease of implementation. If it's just a simple appplication, then just do what you need. If it's a company-wide customer database that all your other applications are going to plug into, then you might need something more flexible...

 

[jfrost10]

I don't know if I agree about grouping address information with an entity being a bad idea...if a requirement is that customer information be unique, then that's up to the application as a whole to ensure that it isn't. In the app my company does, we have checks and alert the user if the information they entered for a customer appears to already exist in the system. But its ultimately up to them whether or not they want to enter it.

I agree with tradeoff statement...at some point, the compplexity that accomodates flexibility can be a hindrance more than a help. For instance, in the example you gave you could go further and say:

1. Attempt to get the statement address for this account
2. If there isn't one, get the owning customer's address
3. If he doesn't have one, identify the customers company
   and use that one.
4. If he has no company associated with him, then check if
   there's someone listed as a spouse who may have an
   address
5. If not, then check a previous statement for this
   customer and see if there's an address
....

At the end of the day, I guess it all boils down to requirements and what the necessary functionality is to meet those requirements.

This has been a very good thread; I've appreciated the discussion on this.


D

 

[dragonwell]

Actually...question to Dragonwell: in almost every app I've seen, the address is actually part of the entity (customer, company, etc.)...are you guys just trying to make you're system uber-robust in that it can handle multiple addresses? Or maybe your requirements call for it...I'm just curious what the overall sceanrio is.

Two very different types of entities - "Drivers" and "Recruiters". Driver has one address, Recruiters have a public and a private address. Originally I had just two main tables - Driver and Recruiter, and stored all the address info in each.

The app requires that Drivers enter their information in steps ("wizard" style) and at some point I realized that I needed to know if the Address information was present or not. It seemed like a bad idea to have a "HasComplatedAddress" bit field, so I figured having the address in a separate table would make it easy to check for "Completed" by just joining the tables, or trying to load it by FK and checking for null. So when recruiters search for Drivers, only the Drivers with addresses (and thus "Complete") will be in the inner joined result sets. So in this way, the business rule is implemented by the schema.

Also, the Address object has a "ToHTMLString()" method that formats the address parts into nice looking html, and I needed it in three places (and that's another subject...)

Thanks for everyone's help.

Greetings,
Dragonwell

 

[wgcs]

jFrost: The Glue table does 'stick' fine: It stores both the ForeignKey and the ForeignTable:

When wanting all addresses for People:

SELECT Person.*, Address.*
  FROM Person 
       LEFT JOIN AddressGlue ON PersonID=AddressGlue.ForeignKey 
                                and AddressGlue.ForeignTable="PERSON"
       LEFT JOIN Address ON AddressGlue.AddressID=AddressID
  WHERE whatever!

When wanting all addresses for companies:

SELECT Company.*, Address.*
  FROM Company 
  LEFT JOIN AddressGlue ON CompanyID=AddressGlue.ForeignKey 
                           and AddressGlue.ForeignTable="COMPANY"
  LEFT JOIN Address ON AddressGlue.AddressID=AddressID

Each case will produce one record for each address (duplicating the person or company), or nulls in the address fields if no address is found.

Here's an example, implemented in VFP:

CREATE CURSOR Person ( personID I AUTOINC, First C(10), Last C(10) )
CREATE CURSOR Company ( companyID I AUTOINC, Name C(30) )
CREATE CURSOR Address ( addID I AUTOINC, Addr1 C(30), City C(10), State C(2), Zip C(5) )
CREATE CURSOR AddGlue ( AddGlueID I AUTOINC, AddID I, ForeignKey I, ForeignTbl C(20), Desc C(30) )

INSERT INTO Person (first,last) VALUES ( 'John', 'Fisher' )
INSERT INTO Person (first,last) VALUES ( 'Joe',  'Schmoe' )
INSERT INTO Person (first,last) VALUES ( 'Jack', 'Daniels' )
INSERT INTO Person (first,last) VALUES ( 'Jim',  'Beam' )

INSERT INTO Company (Name) VALUES ( 'Sager' )
INSERT INTO Company (Name) VALUES ( 'RedHat' )
INSERT INTO Company (Name) VALUES ( 'M$' )
INSERT INTO Company (Name) VALUES ( 'Intel' )

INSERT INTO Address (Addr1,City,State,Zip) VALUES ( '1 Microsoft Way','Redmond','WA','12345' )
INSERT INTO AddGlue (AddID,ForeignKey,ForeignTbl,Desc) VALUES (1,3,'COMPANY','Main Campus')

INSERT INTO Address (Addr1,City,State,Zip) VALUES ( '101 Joes Ln','AnyTown','PA','12345' )
INSERT INTO AddGlue (AddID,ForeignKey,ForeignTbl,Desc) VALUES (2,2,'PERSON','Home')

INSERT INTO Address (Addr1,City,State,Zip) VALUES ( '5 Linux Rd','Another','On','12345' )
INSERT INTO AddGlue (AddID,ForeignKey,ForeignTbl,Desc) VALUES (3,2,'COMPANY','Open')

INSERT INTO Address (Addr1,City,State,Zip) VALUES ( '10 Jacks St','MyTown','PA','12345' )
INSERT INTO AddGlue (AddID,ForeignKey,ForeignTbl,Desc) VALUES (4,3,'PERSON','Home')

SELECT Person.*, Address.*, AddGlue.Desc ;
  FROM Person  ;
       LEFT JOIN AddGlue ON (PersonID=AddGlue.ForeignKey and AddGlue.ForeignTbl="PERSON") ;
       LEFT JOIN Address ON AddGlue.AddID=Address.AddID ;
  INTO CURSOR PerAdd
BROWSE NORMAL NOWAIT 

SELECT Company.*, Address.*, AddGlue.Desc ;
  FROM Company ;
  LEFT JOIN AddGlue ON (CompanyID=AddGlue.ForeignKey and AddGlue.ForeignTbl="COMPANY") ;
  LEFT JOIN Address ON AddGlue.AddID=Address.AddID  ;
  INTO CURSOR CoAdd
BROWSE NORMAL NOWAIT 

RETURN

Final output from these two cursors:

[b]
 PERSONID FIRST  LAST     ADDID ADDR1            CITY     STATE  ZIP    DESC[/b]   
        1 John   Fisher  .NULL. .NULL.           .NULL.   .NULL. .NULL. .NULL.
        2 Joe    Schmoe       2 101 Joes Ln      AnyTown  PA     12345  Home
        3 Jack   Daniels      4 10 Jacks St      MyTown   PA     12345  Home
        4 Jim    Beam    .NULL. .NULL.           .NULL.   .NULL. .NULL. .NULL.
[b]
COMPANYID NAME            ADDID ADDR1            CITY     STATE  ZIP    DESC[/b]
        1 Sager          .NULL. .NULL.           .NULL.   .NULL. .NULL. .NULL.
        2 RedHat              3 5 Linux Rd       Another  On     12345  Open
        3 M$                  1 1 Microsoft Way  Redmond  WA     12345  Main Campus
        4 Intel          .NULL. .NULL.           .NULL.   .NULL. .NULL. .NULL.

 

[jfrost10]

Heh...the glue sticking was my attempt at humor (glue...sticking...address glue...anyway...).

In the way I had suggested (just to compare), it was a table for Customer and a table for Customer Address (and hte address can't be stored with the customer, that's a requirement...but multiple addresses can be shared with a customer).

Classes
-------
Customer
CustomerAddressCollection

Struct
-------
CustomerAddress

DB Tables
--------
Customer
CustomerAddress

SP's
--------
ListCustomerAddressByCustomer
AddCustomerAddress
UpdateCustomerAddress
DeleteCustomerAddress

Now, if we went with the AddressGlue option:

Classes
--------
Address (base class)
Customer
CustomerAddressCollection
CustomerAddress (sub class-knows the AddressType value)
(and Foreign Table value to pass in)

DB Tables
--------
Customer
Address
AddressGlue

SP's
--------
ListAddressByEntity
AddAddress
UpdateAddress
DeleteAddress

A few notes:
- Although at first the ListAddressByEntity looks like it would save space by putting any sort of address retreival into one SP, you're just building a Frankenstein...every new type of address will require this to be edited...and whate if Public/Private becomes Public/Private/Open/Inactive/Previous/Current/...
You'll be constantly modifying this SP. If you like all eggs in one basket, then this isn't an issue.

- From a code point of view, you'll need a few more classes to accomodate the glue. Since different types of addresses have their own custom properties (Company has public/private...person could have multiple as well...), the class should be aware. This means that you can't just have a single type of Address class...you'll need specific ones that can handle the extra logic required. Won't you need to do the same in my example? No...because since the addresses are kept in different tables, their columns would reflect the different attributes of the address (AddressType would become IsPublic (bit) in the Company table).

There's a few other items that could be pointed out...but I think this just pounds home what Steve said: its all about tradeoffs. What are you willing to have suffer? If you create an amazing database schema, but it means that your application developers get more work, is that a fair tradeoff? If it means that you don't have to write a tonne of code, but your database may seem primitive to some DBA's, is that a fair tradeoff?

Again, sorry about the attempt at humour. The glue does stick, and I can see where you're coming from. From an application-running-on-the-database point of view, I just don't agree with it. But then again, I also think the Atlanta Falcons are the best team in teh NFL...and most people would disagree with me too.


D

 

[wgcs]

Again, sorry about the attempt at humour. -- The humour didn't offend me

I very much appreciate hearing debates over RDB structural designs.

It just seemed the design with a general "Glue" table was dismissed out-of-hand, while it seemed to me to be the first approach I'd consider.

Of course, everyone has their own preferences as to what they are willing to trade off.

One principle I've found useful for design, though, is that when there are two (or more) tables that have identical structures, they should be one table, for example, the PersonAddress, CompanyPublicAddress & CompanyPrivateAddress in the one example above, all have practically identical structures.

It seems, code-wise, to be 6-on-the-one, 1/2 dozen on the other, to either:
6) use the table name (PersonAddress, CompanyPublicAddress or CompanyPrivateAddress) in the SELECT to determine which ID is being joined to the AddressID

1/2 dozen) use a filter on the join ( ForeignTable="PERSON", ForeignTable="COMPANY" and Desc="PUBLIC", or ForeignTable="COMPANY" and Desc="PRIVATE", respectively) to determine which ID the AddressID joins to.

Either way, the same code potentially has to change as more tables are added that can have a related Address, or more address types are added to existing tables.

The reason I prefer a single Glue table is that, as the DB design develops over time and another address type needs to be added, or another table needs to have a related address, I don't have to create a new "glue" table (and, if a particular type no longer is needed, I don't have to DROP a table).

 

[stevexff]

Alternatively:

[b]party[/b]
party_id
startDate
endDate
partyType
name

[b]driver[/b]
party_id
...

[b]recruiter[/b]
party_id
...

[b]party_address[/b]
party_id
address_id
address_type_id
start_date
end_date

[b]address_type[/b]
address_type_code
text

[b]address[/b]
address_id
postcode
line1
line2
town
...

This allows you to treat all 'parties' the same when it comes to addressing as the party_id is unique across a family of party-ish tables, so you can have as many or as few address types as you want, e.g.

1   Home
2   Office
3   Billing
4   Statement
5   Credit check
...

This gets rid of the glue altogether.

 

[dragonwell]

stevexff - Thanks! - That is definately the correct answer. It seems so obvious now... :S

Greetings,
Dragonwell

 

[AppDev76]

stevexff,
you provided an interesting model for Address and parties.
But what if different parties require specific attributes for their address.
It appears in this case we will need the CustomerAddress,CompanyAddress tables to link the address to a contact or company and also save the attributes specific to each object. Kind like address subtypes.

 

[stevexff]

AppDev76

Can you give a concrete example? I'm not sure I understand what you mean.

 

[AppDev76]

For example consider a Building Contracting Service Business.
you need to keep track of buildings,companies and people addresses.
For buildings you don't need a Suite Number, so all suite numbers will be NULL for building addresses, but for people you need suite number.