Jump to content

Database table design help urgently needed


Putnammj

Recommended Posts

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

Link to comment
Share on other sites

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.

post-65947-0-23154300-1395871584_thumb.png

Edited by Andy-H
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!!!!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.