addressesmembers and brandaddresses

[patweb]

now I have a table addressesmembers and each record in this table can have one to many brandaddresses with are in an additional table. (members, suppliers are looking for some addresses of certain brands).

I was thinking, won't it be better, to add the brandaddresses to the table addressesmembers (so we have the same structure) and to mark in a field thats its a brandaddress. So then we have a better structure but can you work with this in a form ?
The addressmembers is the head form and the brandaddress is linked with the addressmembers in the head form. So can you have a relationship with data in the same table ?

 

[lespaul]

sounds like the same situation with employees and managers. A manager is an employee. So, yes you can have a "self-joining" relationship in a table.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[patweb]

thanks Leslie, your comparison was matching ! Is it difficult, do you need an extra table, or do you have to make a copy of your addresses table ?

 

[lespaul]

No, just one table. Since I'm not real sure about your structure, I'll continue with the employee manager relationship:

Employees
EmployeeID (PK)
EmployeeFName
EMployeeLName
etc.
ManagerID (FK)

EmployeeID    FName     LName    ManagerID
1             Joe       Blow         
2             Jane      Doe           1
3             Bob       Smith         1
4             Robert    Jordan        2
5             Terry     Goodkind      3

So Joe Blow manages Jane & Bob, Jane manages Robert and Bob manages Terry.

Does that help?

les

 

[patweb]

ok, but one FK won't be enough. Each addressemember can have more then one adressebranch after a while.

headform
addressemember id 1

subform
addressememberbranch fk linked to 1, his own id is 5
addressememberbranch fk linked to 1, his own id is 8
addressememberbranch fk linked to 1, his own id is 12
addressememberbranch fk linked to 1, his own id is 99

 

[PHV]

The FK in your example is the ManagerID.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

addressememberbranch fk linked to 1, his own id is 5

So you're saying that member#5 can be linked to more than just member#1?

 

[patweb]

yes, number 5 could be linked to more then just number#1.

Several members can ask for the same brandaddress.
Several members itself can ask over a year to many brands.

 

[lespaul]

then you have a many to many relationship which needs to be set up differently. You are going to need another table. Instead of adding the field to the addressmember table, create a new table BrandAddresses with two fields, one is addressmemberID and the other is addressememberbranchID; make these fields a composite PK. So your original table now has (assuming that both these entities have the same information):

1 addressmember info
5 brandaddressmember info
8 brandaddressmember info
12 brandaddressmember info
25 addressmember


your new table will look like:
AddressMemberID BrandAddressID
1 5
1 8
1 12
25 5

Now each BrandAddress can be assigned to multiple AddressMembers and each addressMember can have multiple BrandAddress.

Does that make sense?

Leslie

 

[patweb]

yes, I see, if it is well understand to me, in this way I can maintain my addresses in just one table and I can create a many to many relationschip (form and subform) with a between table containing the IDs.

 

[lespaul]

exactly! You should read The Fundamentals of Relational Database Design for a more detailed explanation!

Good luck!

leslie

 

[patweb]

wow, nice reference. I know the basics about relationships but never to late to sharpen the knowledge (there is a first time for everything - like self-joing one table).

pat.