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

  • 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 '17 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 '17 at 7:35
  • 1
    @nd510 - you can ask to have your accounts merged – Max Vernon May 31 '17 at 18:22
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.

  • 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 '17 at 18:28
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

  • 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 '17 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). – Nelz Jun 1 '17 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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