开发者

Help with database structure

I am trying to create a system which allows local network users to collaborate content together.

Requirements: The user should be able to:

  1. create content,
  2. edit content (content created by anyone),
  3. records should be kept on who edited what and when,
  4. should easily be able to go 开发者_Go百科back and forth between edits,
  5. add comments to pages,
  6. should be able to assign rights to created/edited pages (i.e. who can view them)

I am trying to come up with some database structures, but need some help. Could someone please help me create a good structure for such requirements?

EDIT:

This is what I have so far:

page table -
page_id
title
content
date
inactive (0,1)
author

rights table -
page_id
user_id
view (0,1)
edit (0,1)
delete (0,1)

history table -
page_id
title
content
date
inactive (0,1)
author

How could this be improved?


I think most of what you had is fine. I'm just showing additions or changes in the design, quick but rough estimate:

user
----
user_id
network_username ("Or Active Directory Name or whatever, you get the idea.
    Using user_id as a foreign key in other tables instead of the network
    name makes it easier if account names change.")

role
----
role_id
name

user_role
---------
user_role_id
role_id
user_id

page
----
page_id
author_user_id

page_comment
------------
page_comment_id
parent_page_comment_id ("Self reference to allow for nested comments")
page_id ("Even though you could conceivably look up page_id via
    parent_page_comment_id if you're dealing with a nested comment, that
    would involve N nested queries... so it's probably good to require this
    field be populated for every single record")
user_id


permission
----------
permission_id
page_id
role_id ("To simplify, I'd stick solely to role-based access.")

history
-------
history_id ("If you have high-volume edits, you may need to use date AND this
    to establish the order changes were made in.  Probably safest to use both
    right from the start.")
page_id
user_id ("caveat:  you won't know what role the user was in when a change was
    made.")
old_title ("I'd prefix "old_" to all the fields just to make it intuitively
    clear.")


You could take a look at the tools used to build the SO site.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜