database design question

Hello all

I have been asked to create a database that has a users table, news table and a groups table. I'm new to this so I'm having a bit of difficulty mapping out the database design, and was hoping someone here could offer some input.

The idea is that the users will add their own news stories, and assign the stories to certain groups that HAVE been set (default groups) for them already like friends/work/public. But the users can ALSO create extra groups if they need to. So my problem is figuring out the best structure for the group's table.

What I have come up with is this...

Users Table
===========

UserID UserName
1 John
2 Mike
3 Sarah

Groups Table
============

GroupID Group Type UserID
1 Public 1
2 Work 1
3 Friends 1
4 Public 2
5 Work 2
6 Friends 2
7 Public 3
8 Work 3
9 Friends 3
10 FAMILY 2

(you'll notice that mike created his new group called family. The other groups will be automatically created when a new user is added to the system. Their new USERID will be assigned to a public, work and family groupID)

News Table
==========

NewsID UserID GroupID NewsStory
1 3 8 Blah, blah news...
2 3 3 Blah, blah news...
3 2 2 Blah, blah news...
4 1 2 Blah, blah news...

(Each user will be able to assign his/her news story to a certain group)

So my concern is the group's table. There's a lot of data repeating itself, but I can't think of a good way to store newly added groups by users. Is there a better way of doing this?

Thanks for your help!

Honestly, That is the best way to design it. That is the structure of how it should be done 'by the book.' I am pretty sure there is no other way.

It seems like allot of dat is repeated but you are doing it better than most becuase you are storing ID's rather than actually storing the persons name. It saves allot of space. Keep it up and you shall be a pretty good DBA anytime soon.

|||

Hi

The users table and the News table looks fine -- but the Groups table structure, as you rightly observe, does not look very robust. In database design lingo it is not normalized. You would face insert, update and delete anomalies.

It is best to have just one row for each group in the groups table. Hence, you may want to have just one row each for Public, Work and Friends.

Now, you say that users can create extra groups. In this instance, Mike, for example has created the group FAMILY. Is the FAMILY group available only to Mike? Can Sarah, for example, create a FAMILY group as well? I would recommend that the latter not be the case. There should be only one group FAMILY (or better, Family) and the 'Created User' could be Mike. The row created by Mike will be available for use by all other users (think about this -- whether it matches your requirements) Thus your table design would be

GroupID, GroupDescription, CreatedUser
1 Public
2 Work
3 Friends
4 Family 2

NOTE -- The CreatedUser column could be left null (by convention) for seeded groups (default groups).

Hope this helps and do let me know your views on the suggestion. It may not be the best possible solution for your circumstances.

Good Luck!

Fouwaaz

|||

Hi,

Your structure is good. Don't change anything. You can create T-SQL script for creation default groups, so you don't need to do it manually.

|||

Hello all.

Thank you all for your input! I'm going to leave it as is and see how it handles.

Thanks again!

|||

...Sorry sql is not one of my strong points either

If I wanted to select all storys by a user I would user...

SELECT
N.[NewsStory]
FROM
[NewsTable] AS N
INNER JOIN [UsersTable] AS U ON U.[User_ID] = N.[UserID]
WHERE
N.[UserID] = 'number'

but how would I go about selecting only storys for a certain group as well?

What I mean is, how would I select a users newsstorys for say friends only?

thanks again

|||

just do another inner join like you have it. Are you using Visual Studio for this? they have a query builder built in to the program that would allow you to build awesome queries.