CODEWITHSUNDEEP
mysqlsqldatabase-design
user5286438
user5286438

Reputation:

Is this the correct database design? And why am I not

I have two tables, projs and studies, which refer to types of content on the website I'm building, and my latest task is that I need to add image carousels to each project and study (each row of projs and studies). Each project and study needs to have a related image carousel, in other words.

Where I am with my tables: 

   projs
id  |    ...   
----------------
  1 |    ...     
  2 |    ...  
  3 |    ...  

   studies
id  |   ...  
-----------------
  1 |   ...
  2 |   ...
  3 |   ...

Where I want to be or think I should be: Something like

        projs
id  |    ...   | carid
------------------------
  1 |    ...   |   1
  2 |    ...   |   5
  3 |    ...   |   6

       studies
id  |   ...    | carid
-------------------------
  1 |   ...    |  2
  2 |   ...    |  3
  3 |   ...    |  4

     carousels
id  
------
   1
   2
   3
   4
   5
   6

      carousel_assets
id  |   imgurl  | carid
  1 |    ...    |  1
  2 |    ...    |  2
  3 |    ...    |  1
  4 |    ...    |  1
  5 |    ...    |  5
  6 |    ...    |  2
  7 |    ...    |  3
  8 |    ...    |  4
  9 |    ...    |  3
 10 |    ...    |  4
 11 |    ...    |  3
 12 |    ...    |  2
 13 |    ...    |  2

where carid in each table is referencing the id of carousels.

Question 1: Is this the correct design????

The query I ran to create the two new tables was 

CREATE TABLE carousels (id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY;
CREATE TABLE carousel_assets (id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, img VARCHAR (250), carid MEDIUMINT, FOREIGN KEY (carid) REFERENCES carousels(id) ON DELETE SET NULL;

and now what I'm trying to do is add the carid columns to projs and studies. I tried 

ALTER TABLE projs ADD carid MEDIUMUINT, FOREIGN KEY (carid) DEFAULT NULL REFERENCES carousels(id);

and got the very undescriptive error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MEDIUMUINT, FOREIGN KEY (carid) DEFAULT NULL REFERENCES carousels(id)' at line 1

Question 2: What is wrong with my query that caused that error?

On each page, so let's say, /studies/?id=1 which is associated with carousel 2 in my example above, I need to get all the images to associated with that carousel, what should my query be?

Upvotes: 0

Views: 58

Answers (2)

Steve E.
Steve E.

Reputation: 9353

Question 1. Is subjective, but as long as your rules for table design conventions are consistent then it's not bad.

Question 2. Is this the query you require?

ALTER TABLE projs ADD carid MEDIUMINT DEFAULT NULL, ADD FOREIGN KEY (carid) REFERENCES carousels(id);

Upvotes: 0

John Bupit
John Bupit

Reputation: 10618

The error is descriptive enough. You have a typo in MEDIUMUINT. Replace it with MEDIUMINT:

ALTER TABLE projs
ADD carid MEDIUMINT, FOREIGN KEY (carid) DEFAULT NULL REFERENCES carousels(id);

Upvotes: 1

Related Questions