SQL Keys and Relationships question

[zachattack05]

I am in desperate need of some guidance here. Any suggestions or ideas would be very much appreciated!

Is it possible to have a relationship based on two columns? For example:

The table "Parents" has columns:
ParentID (PK)

The table "Children" has columns:
ChildID (PK)
ParentID

The table "Addresses" has columns:
ChildID
ParentID
Address
City
State
Zip

The table "SchoolDistricts" has columns:
ChildID (PK) (Don't let "Child" fool you, it has nothing to do with the Child, it's a "child" of a "Parent" School)
ParentID (there's another table called Schools that is related to this column)

(I know, it's a lame (and probably confusing) example, but the question remains)

Is it possible to create a relationship between the Addresses table and the Children table using BOTH ChildID and ParentID, as well as a relationship between the SchoolDistricts table and the Addresses table using BOTH ChildID and ParentID?

Maybe it's poor design? Is there a better way to do this maybe?

I need to have relationships set up similar to this:

One "Parent" can have many "Children"
One "Child" can have many "Addresses" (amuse me, it happens)
One "School District" can have many "Addresses"

Am I going about this the wrong way? Do I need two tables instead? One to store Children addresses and one to store School District addresses? Seems silly to do that considering they really would have the exact same columns?

[VIeditorlover]

child-*-----------parent-------------*-address

[Momerath]

Yes you can. On SqlServer you'd do something like this:

CREATE TABLE Addresses(
    ChildID int,
    ParentID int,
    Address varchar(100),
    City varchar(100),
    State char(2),
    Zip varchar(9)
    Constraint PK_Addresses PRIMARY KEY(ChildID, ParentID)
)

[gusano79]

Maybe it's poor design? Is there a better way to do this maybe?

I need to have relationships set up similar to this:

One "Parent" can have many "Children"
One "Child" can have many "Addresses" (amuse me, it happens)
One "School District" can have many "Addresses"

Am I going about this the wrong way? Do I need two tables instead? One to store Children addresses and one to store School District addresses? Seems silly to do that considering they really would have the exact same columns?

Right, you wouldn't want to duplicate the table just so both schools and districts could have addresses.

I have a few thoughts about your data model, but before I write anything out, it would help to know a bit more about what you're modeling.

The actual objects you're modeling appear to be:

• Address
• School - Has one or more Addresses; may have one parent School; may have one or more child Schools
• District - Has one or more Addresses; has one or more member Schools

Is that right so far?

Some questions that may be important to the data model:

• Is there any difference between a parent School and child School, or is it just the relationship that's important?
• If a School is a child of another School, is it assumed they both belong to the same District?

[zachattack05]

Thanks Momerath, I have seen that demonstrated before, but I'm not sure if it will achieve what I am after, but I'll have to see...

gusano79,

No, the model you gave is missing a part and a rule. Let me clarify:

One Parent table row can have multiple Children table rows.
One Children table row can have multiple Address table rows

One SchoolDistrict table row can have multiple School table rows.
One School table row can have multiple Address table rows.

The missing rule is, if the School or Parent table has a row, it MUST have at least one row in the SchoolDistrict or Children tables.

For each row in the School and Children tables, there MUST be at least one Address table entry.

Some of these rules are enforced in the business logic and not on the SQL server, but what I need from the relationships setup is so that if a parent or school district is changed or deleted, it cascades down all the way to the address table.

I attached an image that partially represents what I am trying to achieve.

[gusano79]

Oh, okay... I see what I missed there:

there's another table called Schools

So to make sure I understand, you're modeling these things:

• Address, a mailing address
• District, a school district
• School, a school
• Child, a juvenile human being (?)
• Parent, an adult human being (?)

Ignoring databases, tables, and relationships for a moment, are these the actual objects you're modeling?

Maybe it's poor design? Is there a better way to do this maybe?

Am I going about this the wrong way? Do I need two tables instead? One to store Children addresses and one to store School District addresses? Seems silly to do that considering they really would have the exact same columns?

These are the questions I'm working toward answering. Before we can comment on whether your data model is appropriate, we need to understand what you're modeling.

--smg

[zachattack05]

No, they aren't the actual objects. I'd prefer to not state what the objects really are, but for everyone's sanity and to get the right help/suggestions I'll provide as much as needed but as little as possible. They function in a similar way though.

Assume for a moment that you are a business that provides a certain service, and you need to keep a listing of all your clients that use your service so you have a table that has basic information about your client, mostly their company name and an account number that you assign to them.

Now, because your clients sometimes have multiple "branches" you need a way to relate the "Parent" company to the "Branch." Think of the clients like banks, you have Bank of America as an entity (goes in the Company table) and you have the Bank of America on 5th Street in Seattle Washington. So the "Branches" table would hold all of the branches for a company that exist. Each branch though, depending on its size, could have any number of addresses: a billing address, a physical address, a ship to address, a customer service address etc... so great, we have 3 tables:

#1: Companies - Holds general info about the parent company that applies to all branches.

#2: CompanyBranches - holds unique info that applies only to the one branch.

#3: Addresses - holds addresses for all branches (the entry in the Companies table, assuming the Company has only one branch, would still have an entry in the CompanyBranches table, it would just be the "Home" branch)

Now, lets say you not only do business with banks and their branches, but you also do business with vendors, Shopping Malls, Construction companies, whatever, it doesn't really matter what type of business it is. But for each type (like Construction Companies, Shopping Malls etc...) you also need to keep track of Parent companies and any branches they may have. The same rules still apply but because banks and construction companies are vastly different, you can't use the Companies and CompanyBranches table to put construction company information in, so you have 2 more tables:

#4: Construction - holds general info about the parent company that applies to all branches.

#5: ConstructionBranches - holds unique info that applies only to the one branch.

Because they all have addresses, the ultimate question is, can I just store the addresses in one table for all of the different object types, because an address is an address or do I need a different table for each.

I think I might need different tables, because the PK for the CompanyBranches and ConstructionBranches could "repeat" I need to either have a middle table to prevent the keys from repeating, or create an address table for each object type.

Does that help clarify or confuse?

[Momerath]

ParentCompany - main table
Branch - FK to ParentCompany
BankBranch - FK to Branch, holds info specific to banks
ConstructionBranch - FK to Branch, holds info specific to construction
Addresses - Branch would have 1 or more of these

You might need ParentBank and ParentConstruction to hold the info that is unique to those types.

This will allow you to add more types of buisnesses.

[zachattack05]

Momerath,

So you would have a FK in Addresses to Branch as well?

I'm assuming the "Branch" table would just hold a primary key to bridge the gap between the "ParentCompany" table and the various other "BankBranch" and "ConstructionBranch" etc... and a FK to the ParentCompany tables right?

[Momerath]

I'd put a table between address and branch with the key of the branch and key of the address as the two columns (and with those two columns as the key for that table). That gives you a 1 to many from branch to branchaddress and a 1 to 1 for branchaddress to address. Otherwise you end up with a possible many-to-many relation between branch and address (depending on if it is possible for two branches to have the same address. If we are dealing with people, then it is).

[zachattack05]

Actually it WOULD be possible for many different branches to have one address. For example a company might want all bills for all branches to go to one address, or all communications regarding x, y and z to go to the same address regardless of the branch.

Surprisingly that does happen. It's not so much a "hard" relationship between a branch and address, it's more of a "related to..." type relationship.

I think I'm going to play around with this a bit.

Thanks a ton for both of your help and suggestions! I really do appreciate it! This is why I love this forum!

[zachattack05]

OK, I know I'm resurrecting a thread over a week old, but it's better than starting a new one...

This is making me crazy, I can't figure this out.

I'm still working on the same problem.

I have three tables (at least for now): Banks, BankBranches and Addresses.

The PKs for the tables are:

Banks: ParentCode
BankBranches: ParentCode, BranchCode (2 columns, 1 PK)

the addresses table doesn't have a PK yet, that's where my problem lies.

The addresses table will be a catch all for all of the addresses that the system uses, including addresses for Bank Branches and Construction Company Branches (like in the examples before).

The Construction Companies will also have two tables and PKs:

Construction: ParentCode
ConstructionBranches: ParentCode, BranchCode (2 columns, 1 PK)

The problem I'm having is getting a unique enough key into the addresses table.

Since the Parent Code for a Bank, and the Parent Code for a Construction Company are only unique to those particular tables and not unique overall, creating an address table that uses the ParentCode and BranchCode for the PK just won't work.

I thought of creating a column in the address table called something like "AddressGroup" or something so the system could figure out which ParentCode, BranchCode combination we are talking about.

Here's an example of what I mean:

Addresses
----------------------------------------
ParentCode | BranchCode | AddressGroup | (All 3 of these columns would make up the PK)
----------------------------------------
BankA      | BranchA    | Banks
BankA      | BranchB    | Banks
BankB      | BranchA    | Banks
ConstA     | BranchA    | Construction
BankA      | BranchA    | Construction   <= ParentCode and BranchCode match record 1

But if I do this, then I need the AddressGroup column in both the BankBranches and ConstructionBranches tables to create the relationship so if I delete the record with the ParentCode and BranchCode of "BankA, BranchA" from the BankBranches table it only deletes the first record from the example above and not the last one along with it. But that seems so stupid...obviously the records in the BankBranches table will ALWAYS have the value of "Banks" in the AddressGroup column...it seems so silly to waste space on data that will never change, and serves no purpose except to create a relationship.

Is there a better way to do this?

If this is still confusing think about it this way:

There are hundreds of people with the name John Doe.
Some of them bank at US Bank
Some of them bank at Bank of America
Because US Bank and Bank of America are separate entities, their account numbers are only unique to their own institution. So it is perfectly acceptable for US Bank customer John Doe to have an account number of 1234 and it's also perfectly acceptable for Bank of America customer John Doe to have an account number 1234.
If I was going to store the account numbers in a single table, obviously the account number itself wont work as a key because it can repeat, and neither will the name, if I delete one John Doe at US Bank, I don't want it to delete all the John Does at the other banks with the same name and account number. So I would need a way to signify what bank each John Doe belongs to. It just seems silly to create a column in the accounts table that says "BankName" or whatever and make the key the customer name, the account number and also the bank name. Because that means in order for me to create a relationship between the account numbers table and the US Bank table and the Bank of America table, I need to also add the "BankName" column to them as well, but the value would never change...it would always be the same.

I'm so lost...Any suggestions would be GREATLY appreciated

[zachattack05]

ParentCompany - main table
Branch - FK to ParentCompany
BankBranch - FK to Branch, holds info specific to banks
ConstructionBranch - FK to Branch, holds info specific to construction
Addresses - Branch would have 1 or more of these

You might need ParentBank and ParentConstruction to hold the info that is unique to those types.

This will allow you to add more types of buisnesses.

I hate to ask, but is there a way you could draw a diagram showing these relationships? I think I'm having problems visualizing this.

[zachattack05]

I think I found a solution, but it seems messy.

Could someone take a look at the attached diagram and tell me if you see any improvements/problems?

I'd really appreciate it :)