Access Newbie database design question

[sunrise06]

I am trying to learn Access by creating a database from scratch. This database will contain contacts that are members of different groups. There are also spouses that belong to their own set of groups but I want to be able to associate the relationship. Do I need two separate tables, one that will contain the spouses names and contact information or should I put all contacts into one table and add a field for the spouses name?

Any advice will be greatly appreciated.

Thanks

 

[bobsan42]

It would depend on what information (details) you would provide for the spouses.
If every person on the contact list (incl. spouses) will receive the same details then I would do it like this:
all persons (incl. spouses) in one table. Then all connections between them in another.

If all you will provide for the spouse is a name then a field for it in the contacts table should be enough.

 

[sunrise06]

Thanks, same detailed contact information. I am not clear on what you mean by "all connections between them in another". Can you elaborate on that?

 

[bobsan42]

well - you have a table with contacts:

ID | Name
----------
1 | john
2 | paul
3 | ann
...

lets say john is married to ann

then a table with relations:

RelID | Spouse1ID | Spouse2ID
------------------------------
1 | 1 | 3

However - there may be much better solutions out there.

 

[sunrise06]

Thanks. Now what about the group they belong to. There is a regular set of groups and a spouse set of groups. Would you suggest I make two tables or one?

 

[xenou]

Are you saying there are groups that only married couples can belong to? If that's true, just belonging to the group would mean you have a spouse. Why bother keeping track of it separately?

 

[sunrise06]

No, there are groups for the members and there are groups for the spouses. In other words a member could join group A, B, or C and a spouse could join group X, Y, or Z. They can only be a member of one group but they are not in the same group.

 

[xenou]

Which spouse can join X, Y, Z - the spouse thats married to the other spouse, or the reverse?

 

[sunrise06]

Not sure how it matters. Spouse that is married. So let's simplify it by saying all the males can be in group A, B, or C and all females spouses can be in group X, Y, or Z.

 

[OasisUnknown]

With out knowing your situation and exactly what you want. Access is great for building these types of relationships. I just wanted to drop in and give you some key words that you can google that will better lead you to your answer.

What you described is Database Normalization. When you have a correctly Normalized data you have minimized the amount of duplicate data in your database making it easier to build relationships and maintain the data.