kurt.santo Posted March 9, 2008 Share Posted March 9, 2008 Want to create search facility where user can choose the region in step 1. In step 2 he first sees the counties of the chosen region listed and they can choose either all in the county or one particular county to see a listing of companies relevant. In step 3 they are on the listing page with relevant details of companies. In step 1 and 2 would like them to see the number of companies listed behind the name of region/county.How many tables should I create? For regions and counties I was thinking to have region_name, region_nr, county_name,county_nr in table. Or would it make sense to have two tables with the county_nr of the counties table being the foreign key in the region's table? On the other hand: The companies can be listed under one, several or all counties. Should I have for each county a field, which can either be true or false? Or how are you able in php to make the connection between the two? Later on I would like to enable the companies themselves to choose the counties.I appreciate any suggestions.Kurt Link to comment Share on other sites More sharing options...
justsomeguy Posted March 10, 2008 Share Posted March 10, 2008 You'll want one table for regions, one table for counties with a field to say which region the county is in, the companies table, and then a table with 2 columns that associates company IDs with county IDs. Link to comment Share on other sites More sharing options...
kurt.santo Posted March 10, 2008 Author Share Posted March 10, 2008 then a table with 2 columns that associates company IDs with county IDs.That sounds good. I just do not understand how to achieve that for companies, which are in various counties? In this particular table could I have for counties field several entries and it would be still ok to fetch data separately (meaning that if company A is listing in county 4 and 5 it will show independently in both counties listing when user made his/her choice)? OR would I have several combinations as such (company A - County 4, company A - County 5)? Also, at a later stage I want to have the companies being able to select their counties from a admin interface (in case this is relevant for my current question).Kurt Link to comment Share on other sites More sharing options...
justsomeguy Posted March 10, 2008 Share Posted March 10, 2008 If you have a one-to-many relationship then use another table to hold the relationships. So you would need a table with 2 columns, for the county ID and the company ID. A record would indicate that the certain company is in the certain county. If you have a one-to-one relationship, where each company can only be in one county, then you can just use a field in the company table to hold the county ID. Link to comment Share on other sites More sharing options...
kurt.santo Posted March 11, 2008 Author Share Posted March 11, 2008 If you have a one-to-many relationship then use another table to hold the relationships. So you would need a table with 2 columns, for the county ID and the company ID. A record would indicate that the certain company is in the certain county. If you have a one-to-one relationship, where each company can only be in one county, then you can just use a field in the company table to hold the county ID.Each company can be listed in several counties, so I would think I am after a one to many relationship (a company can be only listed once in a county, but can be in any or all counties). You mentioned a separate table to hold the relationship. What does this exactly mean?Kurt Link to comment Share on other sites More sharing options...
justsomeguy Posted March 11, 2008 Share Posted March 11, 2008 Right:a table with 2 columns, for the county ID and the company ID. A record would indicate that the certain company is in the certain county. Link to comment Share on other sites More sharing options...
kurt.santo Posted March 12, 2008 Author Share Posted March 12, 2008 Right:a table with 2 columns, for the county ID and the company ID. A record would indicate that the certain company is in the certain county.So, it would be no problem with regard to normalisation to have the county IDs repeated? For example there could be 50 combinations including same county id, but each time different company id. Because the combination itself is unique it would be ok? Did I get this right? I never really understood normalisation properly.Kurt Link to comment Share on other sites More sharing options...
justsomeguy Posted March 12, 2008 Share Posted March 12, 2008 That's fine. You can make both columns part of the primary key. Link to comment Share on other sites More sharing options...
kurt.santo Posted March 13, 2008 Author Share Posted March 13, 2008 That's fine. You can make both columns part of the primary key.That is great help! Thanks a lot...Kurt Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.