Designing SQL Server database

Question

I'm new to SQL and database design.

I have a task where I need to create a table with users (student or teacher), each user can be in a group or more, and each group can get one or more tasks to solve.

I created the design, but I think is a problem with the entity group, because when I what to add data there, I can't do it (I have only the ID, but no other data. I'm sure I did something wrong).

Adding data to the user entity is working.

What should I change or how I should add users to the group?

I see you needing three tables: Users, Groups, and Tasks (some prefer singular names, that's just me with the plurals). You're on the right track with using IDs for all tables. I'll add an answer with more specifics. — B. Clay Shannon-B. Crow Raven
– B. Clay Shannon-B. Crow Raven, Commented Dec 1, 2015 at 21:49
"when I what to add data there [to group], I can't do it" what data do you want to add? — Shannon Severance
– Shannon Severance, Commented Dec 1, 2015 at 21:54

B. Clay Shannon-B. Crow Raven · Accepted Answer · 2015-12-01 21:55:14Z

2

Something like this should work:

USERS
-----
user_id (pk)
name
surname
email

GROUPS
------
group_id (pk)
groupName

TASKS
-----
task_id (pk)
taskName

USERSGROUPSM2M (Many-to-Many table)
-------------
id (pk)
user_id (fk)
group_id (fk)

You could have user_id #1 with group_id #1 and #42, or any combination

GROUPSTASKSM2M (Another Many-to-Many table)
-------------
id (pk)
group_id (fk)
task_id (fk)

You could have group_id #7 with task_ids #3 and #76, or any combination

answered Dec 1, 2015 at 21:55

B. Clay Shannon-B. Crow Raven

10.7k158 gold badges508 silver badges909 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

thepiyush13 · Accepted Answer · 2015-12-01 21:50:11Z

1

Are trying to add additional information for each group? if so, it would make sense to add more columns in the group table just like the user table and add the group related data in these columns:

group( id, name, type, description)
user(id, name, ...... )

so for example if you want to update the name of any group, just update the group column name for that group id

answered Dec 1, 2015 at 21:50

thepiyush13

1,3211 gold badge8 silver badges9 bronze badges

Comments

Ion · Accepted Answer · 2015-12-01 22:04:28Z

0

Is this a possible way to do it?

 insert into [group] (group_id) values((SELECT ISNULL(MAX([group_id])+1,1) FROM [group] WITH(SERIALIZABLE, UPDLOCK)))

insert into [Entity3]  values(1,1)  //values(x,y) x - user_id, y - group_id where I want to add the user
insert into [Entity3]  values(2,1)
insert into [Entity3]  values(3,1)

This is working as expected, but maybe something shorter or easier?

answered Dec 1, 2015 at 22:04

Ion

3813 silver badges20 bronze badges

Comments

Mike Horstmann · Accepted Answer · 2015-12-01 21:53:55Z

You should probably get rid of the 2 green entities. You can add a user_id value into the group, and the task entities. You may want to also add a "task_id" column to your group. Then you would add your users, and populate group table from that. Then you would assign your tasks by either user_id OR group_id, or both. Also, in the task column you may want to add a "status" column to define if the task is expected complete, or is in error incomplete. Lastly, to be a bit more resource friendly, you can optionally create a "task-completed" table which was user_id, group_id, task_id, and status. Then you can create a process that archives old tasks after a given time frame.

For an excellent resource to build lightweight db's visually you can use (https://www.vertabelo.com/) and once you have the wireframe correct like above it can export to SQL create scripts.

Collectives™ on Stack Overflow

Designing SQL Server database

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related