Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dupati1
Posting Yak Master
123 Posts |
 Posted - 2005-10-17 : 14:49:08
|
| Hello All, I have the following tabletableName: ClientAddressFields:ClientID (pkey)Address (pkey)->custom field and can be populated as we wishAddress1Address2CityStateZipClientId and Address together serve as primary...now the problem is each client can have multiple address...i want to make the combination ClientID and Address as unqiue key for each client and for each addresssituation...first i thought of making the Address to have the concatenation of city and state...something like city+','+statethen i realized that some clients have multiple offices in the same city and state(of course with different Address1(street address) making it a duplicate record....the most important constraint is that the Address field is varchar(20)..so i should fit something meaningful that serves as a key when combined with ClientID and should be in standard format for the clients with/without multiple office locations...any suggestions... |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-10-17 : 15:56:38
|
| I usually create a table of AddressTypes and enforce that each client can have many addresses, but only 1 of each type. So, you then add an AddressType column to your addresses table, and then the PK of the Addresses table become a combination of (ClientID/AddressType).This gives you the most meaningful data, since it forces every entry to be categorized in a meaningful way. Otherwise, you just have a random list of addresses per client w/o any consistency or the ability to determine with accuracy which addresses are offices, versus mailing, versus billing, etc ...Just an idea. |
 |
|
|
|
|
|
merging data to get a unique key