Database table design help urgently needed

[Putnammj]

Hi all,

 

 

Please help it's driving me insane, I'm really hoping someone can help me with this. I have a MySQL database which shows companies that cover certain postcodes (zip codes) within the uk.

 

My first table "members" contains member_id, company_name, contact_number and email address.

 

I need to design a second table to contain the post codes they cover (there are nearly 3000 in the uk). I am really struggling on knowing how to lay this table out and need your advice please! Eg should I have a column for each postcode and write the member_id in that column in a new row if they cover that postcode?

 

Basically i am clear on the first table but I don't know how to lay out the second table for postcodes and how I would link the member_id to the postcodes they cover (some companies will cover 20 post code areas and I understand I can't just have multiple postcode areas in the same column/row? So if member-id 1 and member-Id 2 covered sw1, sw2, sw3 etc how would that work?

 

I just need a clear advice on how that postcode table should be laid out and linked to the first members table so that when a user types a postcode in to the search page e.g SW1 the companies covering that postcode appear.

 

Many thanks in advance! Any advice would be very much appreciated!

 

Mark

[Andy-H]

Hi Mark,

 

I think you would be better splitting this into 4 tables, members, companies, area_codes and company_area_codes, see attachment.

 

This way, companies can have multiple members, and their details do not need to be duplicated. They can also cover multiple area codes without duplication of the area code.

 

Say you wanted to get all area codes that a company covers:

SELECT ac.code FROM companies c
  INNER JOIN company_area_codes cac ON ( c.id = cac.company_id )
  INNER JOIN area_codes ac ON ( ac.id = cac.area_code_id )
WHERE c.id = 1

You can also, of course, join the members table using company_id to get coverage for a member etc.

Edited March 26, 2014 by Andy-H

[Putnammj]

Andy, thanks so much for the reply!!

 

Sorry for not being clear, members and companies are the same thing, the companies would be my members. I want users of the website (no login required) to type the postcode where they live and for companies that cover that location to appear. Companies will cover several postcode areas.

 

Table 1 will have details of the company and how to contact them by phone or email etc and the second table which I will need to link to the first table to will contain postcodes within the uk that they cover. I am unsure how to lay out this second table for postcode areas they cover (there are 3000 postcodes in the uk) and how to link it to the first table? Eg what columns would I need in the postcode table? Maybe like this:

 

Member_id. Sw1. Sw2. Sw3. Every postcode in uk in separate column

 

1 Yes. Yes. Etc

 

Any help would be much appreciated on how to lay out the second table and how to link the two tables so the user can search by postcode

 

Thanks again your help is so very much appreciated!!!!

 

Mark

[Andy-H]

In that case just change company_id to company_name on the members table and change company_area_codes to member_area_codes and change company_id to member_id. (and forget about the companies table)

[Putnammj]

.

Edited March 28, 2014 by Putnammj

[Putnammj]

Thanks Andy you are a star! I think I understand now Please take a look at the image I've attached and let me know if that layout would work ok or if there is a better way!

 

Thanks again I really appreciate your help!

 

Mark

[Barand]

NO!

NORMALIZE!

 

The member area code table should look like this

| member_id |  Code  |
+-----------+--------+
|     1     |  AB1   |
|     1     |  AB2   |
|     1     |  AB3   |
|     1     |  AB4   |
|     2     |  AL1   |
|     2     |  AL2   |
|     2     |  AL3   |
|     2     |  AL4   |
|     3     |  AL1   |
|     3     |  AL2   |
|     4     |  B1    |
|     4     |  B2    |
|     5     |  AB1   |
|     5     |  AB2   |
|     6     |  AB4   |

So now a search on the member column will tell you which areas they cover and a search on the area column tell you which members cover that area

[Putnammj]

Ah thats fantastic thank you!!! That will make the table much smaller and more managable! I had hoped to go this way but i thought that having multiple entries of each member_id would not work. 

 

1) Would i set the primary key as the member_id and would that work ok if it is duplicated in multiple rows?

2) do I not need to make one of the columns "unique"?

 

Thank you so much for the advice!!!!

[Barand]

Put the primary key on both fields. That will prevent duplicate entries.

CREATE TABLE `member_areacode` (
  `member_id` int(11) NOT NULL,
  `area_code` varchar( NOT NULL,
  PRIMARY KEY (`member_id`,`area_code`),
  KEY `idx_area` (`area_code`)
)

[Putnammj]

Thank you thats fantastic will give that a go!

 

Would i then just link the members table to the members_areacode table via the member_id with a one to many relationship?

[Barand]

Exactly. EG

SELECT company_name
FROM members
    INNER JOIN member_area_code USING (member_id)
WHERE area_code = 'AB1'

[Putnammj]

Your a star! Thanks!