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 ;

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.

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:

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.

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 onecompany The record company - there will only be oneperformer May just be one per side of the record but could be as many as sixtitleTrack title - one per sidespeed RPM - one per side ( there was not a standard speed to begin with!)location - one per sidedate recorded - one per sidecatalogue 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 purchaseddate_aq - Date the record was purchasedpurchase - 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

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

album has one row with the ID and a few other detailsperformer has eight rows of ID's and performer namesalbum_performers has two rows linking Luciano Pavarotti & Jose Carreras to album with the ID of 1