2 entities, single record

[swatisonee]

Hi,

I have a table "A" with 2 fields :CID and Companyname.

Now I have a customer who operates from the same address but with 2 different company names. Since all the address contacts are maintained in a separate table that is linked to CID in Table A, how do I show that both companies are actually linked.
If I create the two companies as 2 separate records then i have to create multiple addresses too. Moreover, it wont help me know that the 2 companies belong to the same customer.

I'm missing something simple but i cannot place a finger on it.

Thanks ,

Swati

[scliburn]

have you thought of creating a new column that can link the 2?
Like creating a parent to child relationship?

Maybe creating a field with a true client ID that is present on both records. I imagine you have some type of record id (incrementing) that you use. Having another field would then allow you to link many sub-companies in this manner eh.

hope this give some insight or inspiration.

[swatisonee]

Yes, i did think of adding another field called CID2. But  if the 2nd company gets that as a unique id, then i will need to query each new record i create on whether its got a "parent" or not. The CID i use is autoincrement and primary key btw.
Any other thoughts i could work on ??
Thanks !

[radar]

What you do -- is in the company table -- you have a user_id field -- and what is in this, is the auto_incremented number from the other table...

this way -- its right there who it belongs to.. ;)

[scliburn]

hmmm. here are a few more hacks that i can think of:

#1
creating a new column for alternate company names.
The secondary company name field, could have delimited values that you would have to parse prior to displaying.. its a hack but would work.

#2
or create a separate table for company names and unique id's that are linked to the address table records.

This would essentially give you the ability to add multiple company names for each known address.
so you would have to query all company names from the company name table, and pull the associated address from the address table.

In turn, you can now perform lookups on the company table for each of the names. You could also keep the company name in the address table and refer to it as the primary?

Just throwing random ideas at ya.

[Barand]

Go with #2. You have a one-to-many relationship here between address and company
[pre]
address                              client

id  |  address                      id  | add_id  | name    | etc
----+----------------                ----+----------+---------+------------
1  | somewhere                        1 |    1    |  Co A  | 
2  | the other place                  2 |    1    |  Co B  | 
                                      3 |    2    |  Co C  |
[/pre]

OTOH, if only a couple of companies affected, live with the duplicated address data in client records

[swatisonee]

Thanks a ton all of you. Decided to go with no.2. Its a cleaner option and inthe long run will be more efficient !

Swati