How to make a table with fields that are other tables in MySQL

database-design

I am making a music library system with php in MySQL. In this system there would be a table named Songs. Since many songs can be by the same Artist (Person), I wanted to store a table of Person that contains a persons data (name, DoB, hometown, fan page URL), then just store that ID in the Songs table.

I'm trying to avoid repeating the same 10 fields for an Artist over and over for each of their songs. I'd rather store the ID (Primary Key) and then be able to pull all of their specific data.

Two questions:

What is this called, so I can research how to do it (or can someone give me an example)?
If I do this, will queries for "Barry Manilow", who's ID may be '123', be found in every song that contains the field Artist='123'?

Thanks,
-CJJ
(Basically, what's the best way to reuse fields)

Best Answer

This is a really basic example of normalization.

Typically you set up a foreign key relationship. In your example you would have two tables:

Artists - id, name, etc...

Songs - id, artist_id, name, etc...

You would retrieve needed data from Artists using a JOIN. You should look into some entry level SQL tutorials. JOINs are the bread and butter of SQL, and what makes it a relational database.

Related Solutions

A Huge Database: Picking the right schema and data model

I have not experience such scenario but based on my present knowledge I would suggest you have a table which contains union of all the column which any client can need e.g. say client A wants column 1 and 2 and client 2 wants column 2 and 3 and you have only these two clients then create a table with column 1,2 and 3 and in case of client 1 keep column 3 as null and in case of client 2 keep column 2 as null. Once you are done with this create view for each client e.g.create View A, it should have column 1,2 and will be used in case of client A similarly create view for each client.

Also I would suggest to keep data in table only and not separatly on disc as it create sync issue.

Mysql – How to make a trigger that will compare input with a value of other table

DELIMITER $$  
CREATE TRIGGER Bookings_BeforeInsert BEFORE INSERT ON Bookings
FOR EACH ROW  
BEGIN  
    DECLARE found_flight,dummy,baddata INT;
    DECLARE dt DATETIME;
    SET baddata = 1;
    SELECT COUNT(1) INTO found_flight FROM Flights
    WHERE flight_number = NEW.fk_flight_number;
    IF found_flight > 0 THEN
        SELECT date_of_departure INTO dt FROM Flights
        WHERE flight_number = NEW.fk_flight_number;
        IF date_of_reservation < date_of_departure THEN
            SET baddata = 0;
        END IF;
    END IF;
    IF baddata = 1 THEN  
        SELECT CONCAT('Cannot Insert This Because Age ',NEW.age,' is Invalid')  
        INTO dummy FROM information_schema.tables;
    END IF;  
END; $$  
DELIMITER ;

The IF statement IF baddata = 1 THEN was made a bogus message on purpose. That's because MySQL error handling for Stored Procedures is not designed properly. This crazy workaround I got from Pages 254-256 of the book MySQL Stored Procedure Programming

enter image description here

under the subheading 'Validating Data with Triggers':

I have written about this style of Trigger Programming in my past posts

Apr 25, 2011 : Trigger in MySQL to prevent insertion
Dec 23, 2011 : check constraint does not work?

Best Answer

Related Solutions

A Huge Database: Picking the right schema and data model

Mysql – How to make a trigger that will compare input with a value of other table

Related Question