MySQL > Tables structure

Question

I'm not very savvy in SQL.

Here's the structure of the tables I wanna use:

A user can have multiple courses
A course can be in multiple categories
A category can contain multiple courses
A category can only have one parent (which is also a category)

I have seen multiple setup in here with a 4th table linking categories and courses.

First question: Can I setup the tables this way?

edit: that looks awful, let me fix this...

categories

+---------+------------+----+-------+
|Column   |Type        |Null|Default|
+---------+------------+----+-------+
|id       |int(11)     |No  |       |
|name     |varchar(45) |No  |       |
|parent_id|int(11)     |No  |0      |
|course_id|int(11)     |No  |0      |
+---------+------------+----+-------+

courses

+---------+------------+----+-------+
|Column   |Type        |Null|Default|
+---------+------------+----+-------+
|id       |int(11)     |No  |       |
|name     |varchar(45) |No  |       |
|cat_id   |int(11)     |No  |       |
+---------+------------+----+-------+

users

+---------+------------+----+-------+
|Column   |Type        |Null|Default|
+---------+------------+----+-------+
|email    |varchar(45) |No  |       |
|password |varchar(45) |No  |       |
|firstname|int(11)     |No  |0      |
|lastname |int(11)     |No  |0      |
|birthdate|date        |No  |       |
|ssn      |int(4)      |No  |0      |
|course_id|int(11)     |Yes |0      |
+---------+------------+----+-------+

Second question: How can enter multiple course_id per category?

Thanks for your help

Thanks @bluefeet, I couldn't figure out how to make it look this way! — Aymeric
– Aymeric, Commented Jan 16, 2014 at 22:52
@Strawberry, a user can be enrolled in multiple courses. (Hope I answer your question) — Aymeric
– Aymeric, Commented Jan 16, 2014 at 22:57
If this is for actual production use (and even if it's not) you should at minimum salt and hash the passwords. The last four of their ssn should also not be stored along with their First, Last, and birthdate. As far as database structure, you'll need two additional tables, one linking Users to Courses, and another linking Courses to Categories, since both are many-to-many relationships. Having cat_id in courses and course_id in users makes that a one-to-many relationship which is incorrect — Ghost
– Ghost, Commented Jan 16, 2014 at 23:04

Barry Colebank Jr · Accepted Answer · 2014-01-16 23:08:36Z

1

You need a table for each of your three "items" and then the tables that do the many to one relationships you want between category, course and user.

Note: I have a personal preference for naming things in the singular and the conventions for my constraints. I won't cry if you ignore them.

Note 2: This is mssql code, I haven't tried to use it in mysql because I don't have an instance, but the concept is the same, no matter which relational database you use.

CREATE TABLE [dbo].[Category]
(
    [CategoryId] INT NOT NULL,
    [Name] VARCHAR(45) NOT NULL,
    [ParentId] INT NULL,
    CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED ([CategoryId] ASC),
    CONSTRAINT [FK_Category_Category] FOREIGN KEY ([ParentId]) REFERENCES [Category]([CategoryId])
)

CREATE TABLE [dbo].[Course]
(
    [CourseId] INT NOT NULL,
    [Name] VARCHAR(45) NOT NULL,
    CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED ([CourseId] ASC)
)

CREATE TABLE [dbo].[User]
(
    [UserId] INT NOT NULL, 
    [Email] VARCHAR(45) NOT NULL,
    [Password] VARCHAR(45) NOT NULL,
    [FirstName] VARCHAR(45) NOT NULL,
    [LastName] VARCHAR(45) NOT NULL,
    [BirthDate] SMALLDATETIME NOT NULL,
    [SSN] CHAR(9) NOT NULL,
    CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([UserId] ASC)
)

CREATE TABLE [dbo].[UserInCourse]
(
    [UserId] INT NOT NULL,
    [CourseId] INT NOT NULL,
    CONSTRAINT [PK_UserInCourse] PRIMARY KEY CLUSTERED ([UserId] ASC, [CourseId] ASC),
    CONSTRAINT [FK_User_UserInCourse] FOREIGN KEY ([UserId]) REFERENCES [User]([UserId]),
    CONSTRAINT [FK_Course_UserInCourse] FOREIGN KEY ([CourseId]) REFERENCES [PreferenceType]([PreferenceTypeId])
)

CREATE TABLE [dbo].[CourseInCatergory]
(
    [CourseId] INT NOT NULL,
    [CategoryId] INT NOT NULL,
    CONSTRAINT [PK_CourseInCatergory] PRIMARY KEY CLUSTERED ([CourseId] ASC, [CategoryId] ASC),
    CONSTRAINT [FK_Course_CourseInCatergory] FOREIGN KEY ([CourseId]) REFERENCES [Course]([CourseId]),
    CONSTRAINT [FK_Category_CourseInCatergory] FOREIGN KEY ([CategoryId]) REFERENCES [Category]([CategoryId])
)

answered Jan 16, 2014 at 23:08

Barry Colebank Jr

1,9492 gold badges16 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aymeric Over a year ago

Thanks a lot for your answer. Even if I don't understand all the details I understand the concept and will try to reproduce it.

Aymeric Over a year ago

Ok so I created the 2 tables UserInCourse and CourseInCatergory with the foreign keys that refer to the primary keys but it appears that I can't do that:CONSTRAINT [FK_Category_Category] FOREIGN KEY ([ParentId]) REFERENCES [Category]([CategoryId] Is it because it refers to the same table? In fact, the foreign key parentid that refers to categoryid doesn't make sense to me.

Barry Colebank Jr Over a year ago

You said that a category could belong to another category and can only have one parent. That constraint is there to ensure that the value you put in to that field is itself a category id entry. It's not 100% necessary to enforce this, but it does keep the database "cleaner" by not allowing invalid entries in that field.

Aymeric Over a year ago

Thank you for your explanation @Barry Colebank Jr

symcbean · Accepted Answer · 2014-01-16 22:58:45Z

0

Meleneth answered this - then paniced and deleted the answer (not sure why).

First off, course_id in categories makes no sense if courses are 'in' categories.

A course can be in multiple categories....A category can contain multiple courses

Then that's a many-to-many relationship. The way to respresent this in a relational database (which can only describe 1 to many relationships) is to use an intermediate table to decompose the many-to-many relationship into 2 one-to-many relationships. The foreign key 'cat_id' in courses then becomes redundant too.

Specifically you would create a table with cat_id and course_id as the fields (and primary key).

Similarly if a user can have multiple courses, and presumably a course can have multiple users then these tables should not have foreign keys to each other but a sperate table linking them (user_id and course_id).

answered Jan 16, 2014 at 22:58

symcbean

48.4k6 gold badges63 silver badges99 bronze badges

2 Comments

Aymeric Over a year ago

Thanks for your answer, I will do that. However, just for my personal enrichment, why can't we have a many-to-many relationship?

symcbean Over a year ago

This is as many-to-many relatiosnhip. If you mean why can't you create a many-to-many relationship with 2 tables instead of 3....because its impossible to do this and conform to the rules and strucure of a relational database.

Collectives™ on Stack Overflow

MySQL > Tables structure

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related