SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Correct database structure?

    Hello

    I am creating a database for a website.

    1 table includes the structure of site:
    Code:
    Sitemap:
    id
    Sitemap_id #parent id
    Class #says which class should be loaded
    #some more settings....
    Another includes privilages
    Code:
    Privilages:
    id
    Sitemap_id 
    Groups_id #id of some user group
    #privilages
    Then I was wondering, I have many classes that can be loaded from the sitemap (eg Chat, News, Gallery etc..)and they all use different type of privilages, should I put privilages for each Class in its own table? or should a put all privilages for all classes in the Privilages table?

    Still all classes have some privilages the same eg 'view' but it really doesn't 'mean' the same thing for the classes. They also have a lot of different privilages (eg Gallery has 'addPicture', but not addNews etc...)

    Which way should I go? Create a large table full of 'unused' columns or seperate it to many tables that would though only have one line for each group that matches 1 sitemap line?

    If this is not good explenation I urge you to reply and tell me so and I'll add further details!!!

    Thanks in advance!!!
    - website

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what you have already is a one-to-many relationship from Sitemap to Privileges (a sitemap can have multiple privileges, a privilege belongs to only one sitemap)

    make it a many-to-many relationship and you'll have more flexibility

    take SiteMap_id out of the Privileges table, and create a third "relationship" table

    create table SitemapPrivs
    ( Sitemap_id integer not null
    references Sitemap (id)
    , Privileges_id integer not null
    references Privileges (id)
    )

    example of rows in SitemapPrivs table:

    3 56
    3 58
    4 25
    6 58

    sitemap 3 has privileges 56 and 58, sitemap 4 has privilege 25, and sitemap 6 has privilege 58

    note both 3 and 6 have 58

    rudy

  3. #3
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm, that is way to complicated. The idea is that each group can have special previlages on special part of the site, I do not want to group those previlages to many parts of the sitemap, rather I would have some 'default' previlages for each group.
    Or is the other way maybe better?

    This would make my admin system extreamly complicated...

    The question was more if I should seperate the special previlages to many tables or not.
    - website


Bookmarks

Posting Permissions

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