[help] web dev

[xriko 165]

Hello everyone.

 

Years ago (9 I guess) I started to learn a bit web development (html,css,mysql,..) then stop by lazyness.

Since, I have a "project" that I want to make, so i'm trying to make me again in those studying.

 

I'm planning principaly the base stuff, but want to understand more how I will have to make it work, this is were I need help (mysql stuff)

 

I want that : 

My LIVE description will have the "set_list", I want that each track of setlist refer to her own "id_song" page.

My discography information will have a "CD" list, and each CD will have the "tracklist", and I want that each track refer to her own also.

 

 

As far as I remember, I will have to another table refering to [id_live & id_song]   and [id_disk&id_song], that the way  right?

 

I thing I will  really have to follow more my tutorial for a bit, than tryin to remember my old works...

 

 

If some dev can tell me his advice

[Peace Heavy mk II 7200]

I'm not a database architect myself, but it seems like your set_list property, as well as your tracklist within the CD table, can just be a list of id_songs. Having a 4th table would be over complicating things.

[Zeus 7997]

@xrikoI am in front a real keyboard so now I can help explain this. I will be writing my examples in SQL Server SQL, which is very similar to MySQL but a few small things are different.

 

I will start with the SONG table, because that's going to be your most important table. ID_SONG will be in every other table (or almost every other table) so the work we do here will make it easier later. When thinking about it, a song can belong to one artist and a song can belong to more than one CD or sometimes no CD at all. I would add two values, ID_DISK andID_ARTIST, so that I can link a song back to the CD or CDs it is on and the artist that recorded it. I would also think hard about how you want to represent a song performed live that is never officially released (either live distributed or only ever played live). Also if a SONG is on more than one release it should have more than one row in this table. That is okay.

 

 

CREATE TABLE SONG (

  ID_SONG INT NOT NULL,

  TITLE_JP VARCHAR(300) NOT NULL,

  TITLE_ROMAJI VARCHAR(300) NOT NULL,

  ARRANGER VARCHAR(100) NULL,

  WRITER VARCHAR(100) NULL,

  ID_DISK INT NULL,

  ID_ARTIST INT NOT NULL

)

 

 

For the ARTIST table, you can add other fields that you want to track like years active or the label they are signed to.

 

 

CREATE TABLE ARTIST (

  ID_ARTIST INT NOT NULL IDENTITY(1,1),

  ARTIST_NAME_JP VARCHAR(300) NOT NULL,

  ARTIST_NAME_ROMAJI VARCHAR(300) NOT NULL

)

 

 

For the CD table, you want to add another column so that you can join back to the band that released the CD and any other columns that you want. I would also heavily advise against storing images in your database and find another way to represent them, because that can cause your database to get big really fast, but if you still want to use the special LONGBLOB data type. Also, instead of maintaining a list of tracks that belong to an album in your CD table, store the ID_SONG value next to a new value called TRACKLIST_ORDER, so you can reuse the data in the other table and also keep the order that it was released in the album. It's up to you how you want to store different versions of the same release.

 

 

CREATE TABLE CD (

  ID_DISK INT NOT NULL,

  ID_ARTIST INT NOT NULL,

  FRONT_COVER LONGBLOB,

  TITLE  VARCHAR(300) NOT NULL,

  TITLE_ROMAJI VARCHAR(300) NOT NULL,

  RELEASE_DATE DATE,

  RELEASE_CODE VARCHAR(100),

  PRICE NUMERIC(3,2),

  ID_SONG INT NOT NULL,

  TRACKLIST_ORDER INT NOT NULL,

  EXTRAS VARCHAR(1000)

)

 

 

The LIVE table can stay exactly as it is, except I would change SET_LIST to ID_SETLIST, and BANDS to SUPPORTING_BANDS. ID_SETLIST is a new column that you will use to join into another table (I will explain why shortly) and SUPPORTING_BANDS has the same values as the column ID_ARTIST in your ARTIST table. This means that a live that has two supporting bands before the main act will have two rows in this table. That is okay.

 

 

CREATE TABLE LIVE (

  ID_LIVE INT NOT NULL,

  DATE DATE,

  VENUE VARCHAR(100),

  TOUR_NAME VARCHAR(100),

  TOUR_INFO VARCHAR(500),

  SUPPORTING_BANDS INT,

  DOORS_OPEN DATETIME,

  SHOW_STARTS DATETIME,

  PRICE_RESERVED NUMERIC(3,2),

  PRICE_DOOR NUMERIC(3,2),

  ID_SETLIST INT NOT NULL,

)

 

  Now all you need is a SET LIST table that has the ID of the set list, the ID of the band, the IDs of all the songs played, and the order they are played in. You want another table for SET_LIST because you do not want to store the set list as a string. It will be a lot of work to get the answers back later, especially if you want to look at all the concerts for a particular band. You can get back all the other information such as dates and albums by joining the tables that you need together, so there's no reason to store anything else here.  

 

   CREATE TABLE SET_LIST (   ID_SETLIST INT NOT NULL,   ID_ARTIST INT NOT NULL,   ID_SONG INT NOT NULL,   TRACK_ORDER INT NOT NULL )  

 

I hope all of this made sense.

[xriko 165]

@Zeus  Thank you a lot for all your explaination and your involving.

I will continue to follow my tutorials, I need to really put myself into this, but with your help, I can restructure my "hard base".

I will think about the img store, and for sure about the different version of 1 CD, I had forget there was this specificity...

 

@Peace Heavy mk II   Thank for your answer, tbh i had forgot that we could put more than one id_  in a table.

That's why I really need to learn more ^^"

Edited March 17, 2018 by xriko
missclick

[nick 10212]

Glad to see someone interested in web dev here. Web dev learning curve is probably the steepest one compared to other programming fields (I mean full-stack). It's like you're forming a one-man band literally. So keep it up! I'm a noob too.

Edited March 17, 2018 by nostalgia