Architecture for user-tests / group-tests

I have a use case where a User can perform a Test. This is based on an Invite. So an admin can send an Invite to a User to perform a selected Test. It is also possible to create a Team of several Users and send an Invite to all the users of a Team to perform the Test for that Team.

There are several options to create these models, but I am not sure what is the best way (or perhaps there is even an alternative).

Option 1

• User: id, name
• Team: id, name
开发者_运维百科
• UserTeam: user_id, team_id
• UserInvite: id, user_id
• TeamInvite: id, team_id
• Test: id, user_id, user_invite_id (can be null), team_invite_id (can be null), type [user|team]

Option 2

• User: id, name
• Team: id, name
• UserTeam: user_id, team_id
• Invite: id, user_id (can be null), team_id (can be null), type [user|team]
• Test: id, user_id, invite_id

So is it better to have seperate invites (for teams and users) and link the tests to a team-invite or a user-invite (like option 1). Or the alternative: have a single invite and determine then if it's linked to a team or a user (like option 2)?

Personally, I'd go with your second option.

You might also want to investigate this third option, however. It makes it possible to store an invite sent to multiple teams and users:

user: id, name
team: id, name
invite: id
test: id, invite_id
user_invite: invite_id, user_id
team_invite: invite_id, team_id

You could also have a Team defined as a special User with Team.id being both a Primary Key and a Foreign Key to User.id. You tables would look then like this:

Option 3

    * User: id, name                    --- User data
    * Team: id, name                    --- Team data (name field can be dropped)
    * UserTeam: user_id, team_id        --- User belongs to Team
    * Test: id, description             --- Test definition
    * Invite: id, user_id, test_id      --- Invitation for User to make Test
    * TestDone: id, user_id, invite_id  --- TestDone after User accepted Invitation

So all teams will be users too.

I slighly changed the test-invite after re-reading your description regarding this part.

Sample script:

CREATE TABLE user
( id int NOT NULL AUTO_INCREMENT
, name VARCHAR(20) NOT NULL
, PRIMARY KEY (id)
) ;

CREATE TABLE team
( id int NOT NULL
, teamname VARCHAR(20) NOT NULL
, CONSTRAINT PK_team_id
    PRIMARY KEY (id)
, CONSTRAINT FK_team_id_TO_user_id
    FOREIGN KEY (id)
      REFERENCES user(id)
) ;

INSERT INTO user
VALUES
  (1, 'John')
, (2, 'George')
, (3, 'Mary' )
, (4, 'Team-1')  ;

SELECT * FROM user ;

| id | name   |
| 1  | John   |
| 2  | George | 
| 3  | Mary   |    
| 4  | Team-1 |       

INSERT INTO team
VALUES
 (4, 'Team-One') ;

SELECT * FROM team ;

| id | teamname |  
| 4  | Team-One | 

INSERT INTO team
VALUES
 (5, 'Team-Two') ;

> Cannot add or update a child row: a foreign key constraint fails
> (`test/team`, CONSTRAINT `FK_team_id_TO_user_id` FOREIGN KEY (`id`)
> REFERENCES `user` (`id`))