database design question

[fogofogo]

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!

[aj_nsc]

what you do is create a relational table to relate groupid and userid so your group table will be simplfied to


GroupID Group Type
1 Public
2 Work
3 Friends
4 Family


and your new table will be something like


UserID GroupID
1 1
1 2
1 3
2 1
2 2
2 3
2 4
3 1
3 2
3 3


I've learned database programming through hobbies so there may be a better solution, but this is all I can think of to cut down on the redundancy you are seeing in Group Type.

[TJ111]

I have something somewhat similar to this, though instead of groups I have access levels for each user. Here's the two relevant tables (actually just create a new identical db today so I have the dump files).

CREATE TABLE IF NOT EXISTS `articles` (
`id` int(8) NOT NULL auto_increment,
`title` varchar(30) NOT NULL,
`date` varchar(20) NOT NULL,
`author` varchar(50) NOT NULL,
`contents` mediumtext NOT NULL,
`page` varchar(35) NOT NULL,
`edit_date` varchar(20) default NULL,
`edit_auth` varchar(50) default NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `users` (
`id` int(8) NOT NULL auto_increment,
`username` varchar(50) NOT NULL,
`name` varchar(50) NOT NULL,
`password` varchar(32) NOT NULL,
`level` int(2) NOT NULL default '0',
PRIMARY KEY (`id`)
) ;

Each article is assigned a "page" on creation, then only display on that page. And I can limit who has access to which page depending on each user's "level".

If your planning on having a alrge user base, you'd be better of just using the user's id instead of full name in the articles table, that way you have an exact reference.

[fogofogo]

cheers guys. I'll try those. :)