CODEWITHSUNDEEP
mysqldatabaseprimary-key
Anuj
Anuj

Reputation: 5

Design a table structure

I am new to programming and very new to Data Base. My problem is that I need to create a table faculty

id(pk auto increment)  fact_name  department  subjects

Now my issue is that a faculty can have more then 2 or more departments or subjects, for me it is giving error saying duplicate primary key id.

What I want is

id(pk auto increment)  fact_name  department  subjects 
          1               ABC        1           1
          1               ABC        2           2
          1               ABC        3           3

Please suggest how to design my table where I can insert different data for same primary key or is there any other way.

Code

Create table faculty(
fact_id INT AUTO_INCREMENT,
fact_name varchar(20),
fact_email varchar(20) unique,
fact_password varchar(20),
year_id varchar(2),
sem_id varchar(2),
dept_id varchar(2),
subject_id varchar(2),
primary key ( fact_id,subject_id)
)

Upvotes: 0

Views: 42

Answers (1)

Mohammad AbuShady
Mohammad AbuShady

Reputation: 42799

A primary key is a unique identifying key, it can't be duplicated no matter what, if you want another duplicate key then add your own extra field

---------------------------------------------------
| pk | my key | fact_name | department | subjects |
---------------------------------------------------
|  1 |    1   |    ABC    |     1      |     1    |
|  2 |    1   |    ABC    |     2      |     2    |
|  3 |    1   |    ABC    |     3      |     3    |
---------------------------------------------------

Ok according to what I understand now, you need a many to many relationship between subjects, departments and faculties, first split the faculty table

-------------------------------
| pk | fact_name | fact_email | 
-------------------------------

Then create a different table for example called faculty_subjects

---------------------------------------------
| pk | fact_id | department_id | subject_id |
---------------------------------------------

any extra details will be in the join table, not the faculty table.

Upvotes: 2

Related Questions