Best way to structure sql database for records that will have group#, & member#?

[ghurty]

I am working on a script that will need to write data to many "users" assigned to different "groups". From the same page, but it will write to only one group at any "submit" button click.

 

Which group/member # to be written to will be chosen based on user input.

 

Which one of the following options is best for me to structure the sql database:

 

1) One table that has for each record; a group number, and a globally unique member number?

 

OR

 

2) A table for each group #, and and each member number is unique only to that table?

 

OR

 

3) A different option that I am missing.

 

In theory, the amount of records database wide, can grow to be in the tens of thousands.

 

Thanks

[Ken2k7]

Your options aren't exactly clear to me. Here's my input if I understand you correctly -

 

Table group

id PK

group_name

 

Table member

id PK

member_name

 

Table membergroup

id

member_id FK

group_id FK

 

Assuming a member can have more than one group, that's how I would do it. Sorry if that's not what you meant.

[Daniel0]

You don't need the id column in the membergroup table. You already have a composite PK. Not that it hurts anything, but it's just redundant.