Linked table design question

[James]

Hello All,

I have a membership database. I have a table for members, and a table for
their payments linked one to many. This works great.

However, Now I have to add a company table, as companies want to join us as
well. For this I need to link the members to the company table one to many,
not all members will be in a company, but every company will have a member.

Now the head scratching begins when I need to sort out the payments table,
as the companies will be making payments, but so will the members who are not
part of a company.

How do I link the payments table to two tables, or do I set up two payment
tables, one for companies and one for members? Or is there another way I have
missed?

Any advice would be great.
Thanks
James

 

[Klatuu]

Why can't a company just be a member? You could always add a field to your
table to tell you if the member is a person or a company.

 

[James]

Hi,

Could do that, but then how do I go to a company and see which members are
registerd to that company if they are all in the same table? I need a one to
many relationship as a company could have 30-40 staff registered with us.

Cheers
James

 

[Klatuu]

Does a company make payments, or do the members make payments?
If you need to associate members to companies, then a company table would be
correct with a foreign key in each member's record to show the company
association.
If companies do make payments, then how about a record in the member table
that would be the company member?

 

[James]

Hello,

Yeah a company makes payments, and mostlikely will pay for their staff
membership.
If i add a record to the members table for each company wont that make it
rather messy haveing duplicated data in different tables?

 

[Klatuu]

Well, that depends on how you design it. If a company will be the dues for
the members associated with the company. It may be that you will need to
write some code or use a query that will update the payment information for
each member when the company makes the payment.

 

[James]

Hi

Yes I guess the company could pay for the members, or they may not. This is
something new so we are not 100% sure .

But the company has its own fees to pay that are relative to it and no
member. For example if they buy a product.

 

[Klatuu]

Even more reason that there be a company "member". The difficult part will
be when and if a company pays dues or fees on behalf of their memebers. In
this case, you will have to design a way for the data entry person to apply
the payments to the members accounts. This is really more of a business
procedural decision than a technical decision.