Join Mysql tables; 1 contains the names, the other contains multiple links to names

Rubble · October 30, 2013, 6:14pm

I am at a loss here and am not sure what to search for!

I have a table “name” with performer_ID and performer_name.

I have another table ( association ) with a lot of data including 12 name cells which contain the performer_ID number from the “name” table.

The reason for this is that the performer can be in any one or more than one of the 12 cells.
I have a similar problem to the one above for location but there are only two links in the association table this time.

I also have a similar situation for source and here I use a simple join. This works well as the source is only linked in one place.

When displaying the data I need to select the name from the performer table for each of the performers in the association table.

How would I get around the performer problem?

Should I have designed the database in a different way, possibly with a intermediate table?

These are the three tables I mentioned above and the current join I am using.
CREATE TABLE IF NOT EXISTS performer (
per_ID smallint(4) NOT NULL AUTO_INCREMENT,
full_name varchar(50) NOT NULL DEFAULT ‘Name’,
PRIMARY KEY (per_ID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=‘List of performers’ AUTO_INCREMENT=19 ;

CREATE TABLE IF NOT EXISTS source (
sou_ID smallint(4) NOT NULL AUTO_INCREMENT,
origin varchar(50) NOT NULL DEFAULT ‘Name’,
PRIMARY KEY (sou_ID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=‘Where the record came from’ AUTO_INCREMENT=2 ;

CREATE TABLE IF NOT EXISTS location (
loc_ID smallint(4) NOT NULL AUTO_INCREMENT,
place varchar(50) NOT NULL DEFAULT ‘Place’,
PRIMARY KEY (loc_ID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=‘List of performers’ AUTO_INCREMENT=5 ;

CREATE TABLE IF NOT EXISTS association (
ass_ID int(5) NOT NULL AUTO_INCREMENT COMMENT ‘Identifier’,
performer_1 int(4) NOT NULL COMMENT ‘Artist A1’,
performer_2 int(4) DEFAULT NULL COMMENT ‘Artist A2’,
performer_3 int(4) DEFAULT NULL COMMENT ‘Artist A3’,
performer_4 int(4) DEFAULT NULL COMMENT ‘Artist A4’,
performer_5 int(4) DEFAULT NULL COMMENT ‘Artist A5’,
performer_6 int(4) DEFAULT NULL COMMENT ‘Artist A6’,
performer_7 int(4) NOT NULL COMMENT ‘Artist B1’,
performer_8 int(4) DEFAULT NULL COMMENT ‘Artist B2’,
performer_9 int(4) DEFAULT NULL COMMENT ‘Artist B3’,
performer_10 int(4) DEFAULT NULL COMMENT ‘Artist B4’,
performer_11 int(4) DEFAULT NULL COMMENT ‘Artist B5’,
location_A int(4) NOT NULL COMMENT ‘Where side A recorded’,
location_B int(4) NOT NULL COMMENT ‘Where side B recorded’,
source int(4) NOT NULL COMMENT ‘Source of the record’,
PRIMARY KEY (ass_ID),
UNIQUE KEY number (number)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


SELECT * FROM association
JOIN
performer

ON
performer.per_ID=association.performer_1

JOIN
source

ON
source.sou_ID=association.source

JOIN
location

ON
    location.loc_ID=association.location_A

WHERE
number = $number ");

r937 · October 30, 2013, 7:04pm

i am just going out the door so no time to go into specifics

google “first normal form”

redesign your tables and try again

i hope to be back online tomorrow

Rubble · October 31, 2013, 10:00pm

I am afraid I do not understanding what I am finding on the web and need a pointer.

I understand I now need a table containing a performer_ID and a name_ID between the names table and the association table.

The names table will stay as it is but the association table is the confusing part. I will still need the 12 performer columns but will the performer value stay the same for every record or will there be a new performer value for each record.

e.g
Record 1
performer_1 = 1
performer_2 = 2
"
"
Record 2
performer_1 = 1
performer_2 = 2
"
"
OR
Record 1
performer_1 = 1
performer_2 = 2
"
"
Record 2
performer_1 = 11
performer_2 = 12
"
"

r937 · October 31, 2013, 10:20pm

could you do a small exercise for me please

take each pair of your entities and describe two relationships, one in each direction

for example…

each order belongs to only one customer
each customer can have multiple orders

in your case, i have yet to understand what kind of a thing a performer is associated with

Rubble · November 1, 2013, 8:53am

I will have at a guess 2000 performers and 3000 records.

To save space and insure that the performers name is always spelt the correct way during input I have a table containing the names which I look up to populate the input form.
The performer value in the main table will contain the ID of the name so when I display the data the main table will look in the names table for the name and display that not the name ID.

I would describe it as one direction: each performer has one name.

[B]BUT now thinking about this after digesting your question; to also search which records are linked to each performer it should be:

Each performer has one name
Each name has many record numbers.[/B]

I will try and illustrate this a bit better later when I have time.

DarthGuido · November 1, 2013, 9:09am

I believe the table name “association” doesn’t describe the entity, it describes the table’s purpose. Which makes it harder to understand the concept of normalization.

“perfomer” is ok, “location” also.

Maybe the entity that is missing is “play” ? Just guessing of course.
Then you could say (Rudy’s exercise):

A performer can participate in multiple plays
A play can have multiple performers

Etc.

Rubble · November 1, 2013, 9:54am

Thanks for trying to clear up the confusion Guido2004; hopefuly this diagram should help:

The performer in the main table contains the ID of the performer name in the name table.
When I display the data I want the performers name displayed and not the ID.

The location in the main table contains the ID of the location in the location table.

When you search for the records containing a certain performer it will display all the data in the association table with the ID’s replaced by the names and locations.

r937 · November 1, 2013, 11:14am

so you are associating performers to albums?

each album can be recorded at multiple locations
each location can have multiple albums recorded there
… so album-location is many-to-many

each performer can be recorded on many albums
each album can have many performers
… so performer-album is many-to-many

would you agree with this analysis?

Rubble · November 1, 2013, 11:29am

each album can be recorded at multiple locations
each location can have multiple albums recorded there
… so album-location is many-to-many

each performer can be recorded on many albums
each album can have many performers
… so performer-album is many-to-many

Yes I agree with that @r937 ;

r937 · November 1, 2013, 12:57pm

okay, great, so you need three basic entity tables –

performer ( id, name, other attributes )
location ( id, name )
album ( id, size, title, source )

with me so far?

there is more to come

Rubble · November 1, 2013, 2:28pm

Yes I have those already

r937 · November 1, 2013, 2:49pm

oh? that’s not what you’ve been posting

up till now, there was only performer and location, and a very dubious “association” or “main” table that will, frankly, have to go

Rubble · November 1, 2013, 3:32pm

These are the tables I have:


-- Table structure for table `association`

CREATE TABLE IF NOT EXISTS `association` (
  `ass_ID` int(5) NOT NULL AUTO_INCREMENT COMMENT 'Identifier',
  `number` int(5) NOT NULL COMMENT 'File Number',
  `company` int(4) NOT NULL COMMENT 'Record company',
  `performer_1` int(4) NOT NULL COMMENT 'Artist A1',
  `performer_2` int(4) DEFAULT NULL COMMENT 'Artist A2',
  `performer_3` int(4) DEFAULT NULL COMMENT 'Artist A3',
  `performer_4` int(4) DEFAULT NULL COMMENT 'Artist A4',
  `performer_5` int(4) DEFAULT NULL COMMENT 'Artist A5',
  `performer_6` int(4) DEFAULT NULL COMMENT 'Artist A6',
  `performer_7` int(4) NOT NULL COMMENT 'Artist B1',
  `performer_8` int(4) DEFAULT NULL COMMENT 'Artist B2',
  `performer_9` int(4) DEFAULT NULL COMMENT 'Artist B3',
  `performer_10` int(4) DEFAULT NULL COMMENT 'Artist B4',
  `performer_11` int(4) DEFAULT NULL COMMENT 'Artist B5',
  `performer_12` int(4) DEFAULT NULL COMMENT 'Artist B6',
  `title_A` int(4) NOT NULL COMMENT 'Track name side A',
  `title_B` int(4) NOT NULL COMMENT 'Track name side B',
  `speed_A` int(6) NOT NULL COMMENT 'Speed side A',
  `speed_B` int(6) NOT NULL COMMENT 'Side B speed',
  `location_A` int(4) NOT NULL COMMENT 'Where side A recorded',
  `location_B` int(4) NOT NULL COMMENT 'Where side B recorded',
  `date_A` date NOT NULL COMMENT 'Date side A recorderd',
  `date_B` date NOT NULL COMMENT 'Date side B recorderd',
  `catalogue` varchar(12) NOT NULL COMMENT 'Catalogue number',
  `size` enum('8','10','Other') NOT NULL COMMENT 'Record size',
  `source` int(4) NOT NULL COMMENT 'Source of the record',
  `date_aq` date NOT NULL COMMENT 'Purchase date',
  `purchase` int(6) NOT NULL COMMENT 'Purchase price',
  `est_value` int(6) NOT NULL COMMENT 'Current estimated value',
  `sold` int(6) NOT NULL COMMENT 'Sold price',
  `matrix_A` varchar(12) NOT NULL COMMENT 'Matrix number',
  `matrix_B` varchar(12) NOT NULL COMMENT 'Matrix number',
  `notes` varchar(255) DEFAULT NULL COMMENT 'Any notes',
  PRIMARY KEY (`ass_ID`),
  UNIQUE KEY `number` (`number`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------


-- Table structure for table `company`

CREATE TABLE IF NOT EXISTS `company` (
  `comp_ID` smallint(4) NOT NULL AUTO_INCREMENT COMMENT 'Identifier',
  `name` varchar(50) NOT NULL DEFAULT 'Enter company' COMMENT 'Record company name',
  PRIMARY KEY (`comp_ID`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Record company names' AUTO_INCREMENT=12 ;

-- --------------------------------------------------------

-- Table structure for table `location`

CREATE TABLE IF NOT EXISTS `location` (
  `loc_ID` smallint(4) NOT NULL AUTO_INCREMENT,
  `place` varchar(50) NOT NULL DEFAULT 'Place',
  PRIMARY KEY (`loc_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='List of performers' AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

-- Table structure for table `performer`

CREATE TABLE IF NOT EXISTS `performer` (
  `per_ID` smallint(4) NOT NULL AUTO_INCREMENT,
  `full_name` varchar(50) NOT NULL DEFAULT 'Name',
  PRIMARY KEY (`per_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='List of performers' AUTO_INCREMENT=19 ;

--

There is a titleA & B as well as a source column which I have not decided how to deal with yet. I was waiting to see how the performer part worked and go from there.

The size and speed were going to be small integers anyway and I did not see any point in having separate tables for those.
Catalogue number, matrix A and B numbers as well as the dates were all going to be saved into the main table as it was unlikely there were going to be any of those that were the same and I thought it would be simpler.

The company table worked with a simple join in the test I have tried and I assumed it would carry on that way. Again I know now it is a many-to-many association :

SELECT * FROM association
JOIN
company
ON
company.comp_ID=association.company

WHERE number = $number

I am sorry for the confusion but I was thinking that if I had the performer part working I could then modify the code for anything else like the location.

r937 · November 1, 2013, 4:51pm

i think you’re trying to get ahead of me, and was trying to lead you through the steps logically

rename your association table as the album table, and remove all the performer columns

regarding locations, are you saying that there will only ever be at most 2 locations, one for each “side” of the album?

we’re talking vinyl here, right?

Rubble · November 1, 2013, 5:16pm

Sorry r937 as that was what I had before we started and I am happy to start over as I want to see how it all comes together.

We are talking 78’s which I am told have one track on either side. It is not my area of expertise and I am trying to help someone else and the information he wants is everything listed in the association table.

number The owners file number as he has the information on paper already - there will only be one
company The record company - there will only be one
performer May just be one per side of the record but could be as many as six
titleTrack title - one per side
speed RPM - one per side ( there was not a standard speed to begin with!)
location - one per side
date recorded - one per side
catalogue Catalogue number - one per record
size - Record size - one per record ( there was not a standard size to begin with!)
source - Where the record was purchased
date_aq - Date the record was purchased
purchase - Purchase price’,
est_value - Current estimated value’,
sold - Sold price ( default of 0 if not sold )
matrix - one per side ( alphanumeric codes on the record )
notes - Any notes - one per record

I have renamed my table to album and removed the performers.

r937 · November 1, 2013, 5:25pm

okay, now we’re getting somewhere

here’s the key point – unless you are going to want to write insanely complex queries, or the number of albums is going to climb into the millions, i cannot imagine that table scans, which normally should be avoided, will adversely affect the performance of your application

therefore, we can bypass many of the normal rules and conventions for database design in favour of simplifying the structure for ease of maintenance

“lookup” tables, which typically contain an id and a name, are not required here, and you can simply store the name (e.g. in your original source table) as a VARCHAR column, so 2 location colulmns in the album table

you do, though, need a many-to-many table between your albums and your performers –

CREATE TABLE album_performers
( album_id INTEGER NOT NULL
, performer_id INTEGER NOT NULL
, PRIMARY KEY ( album_id, performer_id )
);

Rubble · November 1, 2013, 5:47pm

therefore, we can bypass many of the normal rules and conventions for database design in favour of simplifying the structure for ease of maintenance

Sounds good to me.

I have created that table.

r937 · November 1, 2013, 5:52pm

you okay with what to put in it?

Rubble · November 1, 2013, 6:15pm

I Now have this information in three of the tables:

INSERT INTO `album` (`album_id`, `title`, `size`, `source`) VALUES
(1, 1, '10', 1);

INSERT INTO `album_performers` (`album_id`, `performer_id`) VALUES
(1, 1),
(1, 3);

INSERT INTO `performer` (`per_ID`, `full_name`) VALUES
(1, 'Luciano Pavarotti '),
(2, 'Enrico Caruso'),
(3, 'Jose Carreras '),
(4, 'Placido Domingo'),
(5, 'Maria Callas'),
(6, 'Joan Sutherland'),
(7, 'Kiri Te Kanawa'),
(8, 'Anna Netrebko');

album has one row with the ID and a few other details
performer has eight rows of ID’s and performer names
album_performers has two rows linking Luciano Pavarotti & Jose Carreras to album with the ID of 1

r937 · November 1, 2013, 7:48pm

beauty!