4

I am building a website as a personal project just for some experience in a new web framework and want to make sure I model my SQL database in a good way with the appropriate relationships before building it out and getting myself in too deep. I'm coming over from using MongoDB so this will be my first application in which I have to model the SQL database.

This application will allow users to join and create groups for discussion, add categories to the groups and then post topics to a specific category in a group.

My application will have four main things: Users, Groups, Categories and Topics (assuming no replies to topics, just a simple posting for users to read).

My current advance

This is what I have so far (FK = Foreign Key):

User

  • id
  • username
  • password

Group

  • id
  • title
  • description
  • owner (FK user-id)

Category

  • id
  • title
  • group (FK group-id)

Topic

  • id
  • title
  • author (FK user-id)
  • category (FK category-id)

Membership

  • id
  • group (FK group-id)
  • user (FK user-id)

Responses to comments

The rules are:

  • A user can own/create multiple groups and can join multiple groups
  • A user can create multiple categories within the group that they own/created
  • Members of a group can create multiple topics within a category
  • There can't be duplicate categories within the same group

I am in no capacity a database expert and took one course on database a year ago so any feedback is appreciated

Improve this question
3
  • 1
    Please clarify: [1] May multiple groups use (one and) the same category? [2] May users join multiple groups? [3] May the same topic be used in multiple categories? [4] What happens if 2 users create a group with the same name? Would that be allowed? - It would be a good idea to gather as many "business rules" as possible before drawing a diagram. Avoid getting "implementation details" into the way too early (PK, FK etc). If there are any more business rules you can think of, then please let us know.
    – stefan
    May 31, 2017 at 6:32
  • I accidentally asked this question on that Bobby account and don't know the email or password to log in. So to answer your question, the rules are that a user can own/create multiple groups and can join multiple groups, a user can create multiple categories within the group that they own/created and members of a group can create multiple topics within a category. There can't be duplicate categories within the same group.
    – nd510
    May 31, 2017 at 7:35
  • 1
    @nd510 - you can ask to have your accounts merged
    – Hannah Vernon ♦
    May 31, 2017 at 18:22

2 Answers 2

Reset to default
2
+50

I think it looks about right for the defined business rules that you name... There's probably more to it but your structure seems pretty much whats needed in this case.

I would probably separate the Categories altogether, and then add a new table to store categories per group that way you dont end up with a bunch of duplicated categories on that table.

Something like:

enter image description here

Improve this answer
2
  • 1
    I like it. Could you explain why you gave your join tables (GroupMembers and GroupCategories) an extra PK column? Couldn't you use the two other columns as a composite key?
    – Spivonious
    May 31, 2017 at 18:29
  • @Spivonious Hey there! Yeah, I could have, its a glitch on my head, I generally create separate keys (unless absolutely unreasonable/non-functional).
    – Nelson
    Jun 1, 2017 at 11:42
0

In any Database, you have to normalize your all table structure.

So you just have to do that.

In category table you have to add one column CreatedBy and give references of User table.

It is necessary to know who is created category and also add another one column is CretedDateTime Datetime()

Other table is ready to use because it is already normalize.

Improve this answer
1
  • That isn't what normalize means. His structure is already pretty normalized, he's just missing some many-many join tables that Nelson's answer covered.
    – Spivonious
    May 31, 2017 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.