www.webdeveloper.com
Results 1 to 4 of 4

Thread: database design question

Hybrid View

  1. #1
    Join Date
    Dec 2005
    Posts
    58

    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!
    Last edited by fogofogo; 11-06-2007 at 01:17 PM.

  2. #2
    Join Date
    Dec 2005
    Posts
    2,984
    what you do is create a relational table to relate groupid and userid so your group table will be simplfied to

    Code:
    GroupID Group Type
    1          Public
    2          Work
    3          Friends
    4          Family
    and your new table will be something like

    Code:
    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.

  3. #3
    Join Date
    May 2007
    Location
    Cleveland, OH
    Posts
    1,403
    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).
    Code:
    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.

  4. #4
    Join Date
    Dec 2005
    Posts
    58
    cheers guys. I'll try those.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles