CODEWITHSUNDEEP
mysqlsql
Aymeric
Aymeric

Reputation: 15

MySQL > Tables structure

I'm not very savvy in SQL.

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

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

Upvotes: 0

Views: 67

Answers (2)

Barry Colebank Jr
Barry Colebank Jr

Reputation: 1949

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])
)

Upvotes: 1

symcbean
symcbean

Reputation: 48387

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).

Upvotes: 0

Related Questions