Database structure

[Ovisss4]

Hi

I wonder how could I create database table the right way. So I have users and they are in groups. One group can have many users,but user can be just in one group. And there are modules and module pieces. And there are user right's to module piece. So user can have right to module piece by it's right or by group right. So I don't know how to solve this user right, group right thing.

[VulsaviiK]

You will probably need two tables.

 

One which contains the group, one which contains the users

 

Here's an example Schema (If you don't know what that word means, I encourage you to do some research, and this will make more sense)

 

GROUPS

Group_Name

Group_ID

Group_Rights

etc.

 

USERS

Username

Password

First Name

Surname

Group_ID*

etc.

 

etc.

 

Ah, group ID appears twice, theres a good reason for that. Basically you want to avoid repeating information in the same column. Where that's likely to happen, you can split the tables so that there is an overlap between them, this is tricky to explain in a short post, but do some research on Database Normalization and it's various levels.

 

A proper explanation of this is like, several pages long - but I hope that's a good starting point for you

[SSL]

Your question of permission or rights management bridges both schema and business logic. So, work on separating the representation of permissions and their application.

[madknight3]

12 hours ago, Ovisss4 said:

Hi

I wonder how could I create database table the right way. So I have users and they are in groups. One group can have many users,but user can be just in one group. And there are modules and module pieces. And there are user right's to module piece. So user can have right to module piece by it's right or by group right. So I don't know how to solve this user right, group right thing.

One option is to list out all the different pieces of information you need to work with. These are what I gathered from your description.

• User
• Group
• Module
• Module Piece
• User Right
• Group Right

Next decide what information you need to store for each item and the relationships between them. Then start mocking up tables (pencil/paper or whatever is fine, you don't need to start writing SQL yet). Each of these items may or may not end up as individual tables. You may find that one table can just be part of the columns in another table. Or you may find that one table can really be broken up into multiple tables. You may also find out that by working through the requirements, you will find the need to add additional tables that you didn't think of before or to store relationship information between tables.

 

Lets take an example of converting these items into tables.

I'll use this format. 

• Table Name
  • Column 1
  • Column 2
  • etc

The naming convention you use for the table/column names is up to you. So lets start with these two simple tables.

• User
  • Id
  • Name
• Group
  • Id
  • Name

Now we know there's a relationship between these two tables. Given your description, we have a One To Many relationship (One Group can have Many Users). Adding a column to the Group table that stores a bunch of users (ex: a comma separated string) is generally a bad practice and should be avoided. Instead, you can simply add a column to the User table that stores the group id. Example

• User
  • Id
  • Name
  • GroupId
• Group
  • Id
  • Name

If the requirements change, and you happen to allow a user to join multiple groups, then that changes the One To Many relationship into a Many To Many relationship. In that case, you would want to create a separate table.

• User
  • Id
  • Name
• Group
  • Id
  • Name
• User_Group (or whatever name you think is fitting for a table)
  • UserId
  • GroupId

Note that you could use a separate table for a One To One or One To Many relationship, but it's not necessary. It might be worth considering if you're adding additional information to the relationship though (ex: a column that stores the date/time that a user joined the group). The idea being using the User_Group table instead of adding extra columns to the User table for each additional piece of information you want to store about the relationship.

 

Here's some more information on modelling Many-Many Relationships. Even if you don't need them for the User/Groups example, you might for your other tables.

[vorticalbox]

On ‎25‎/‎02‎/‎2017 at 6:20 PM, VulsaviiK said:

Ah, group ID appears twice, theres a good reason for that. Basically you want to avoid repeating information in the same column. Where that's likely to happen, you can split the tables so that there is an overlap between them, this is tricky to explain in a short post, but do some research on Database Normalization and it's various levels.

 

A proper explanation of this is like, several pages long - but I hope that's a good starting point for you

The explanation is foreign keys.

 

a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key in the first table.

 

more like several lines not pages.

[VulsaviiK]

On 27/02/2017 at 10:54 AM, vorticalbox said:

The explanation is foreign keys.

 

more like several lines not pages.

 

Yes and no. Foreign keys was the word I was looking for correct, you use foreign keys to normalise a database Normal Forms are still important to know when you're working with databases, and it's a subject lengthier than I'm prepared to type for a question that wasn't worded very well from the start