Database design help
I am trying to build an online business directory for a client (like a phone book). The information is simple - I have a list of several thousand businesses and their information (address, phone, etc), and a list of close to 400 categories (Auto parts, bookkeeping, etc).
The problem I have is how to assemble them into a database. Each category, of course, will contain a multiple number of businesses. The problem I'm running into is that some businesses are also listed in multiple categories (Business A is listed under categories 5, 10, and 16).
What would be the most efficient way to set up the database? I prefer to have each business listed only once, so that if information changes, or if they are added or removed from one or more categories, it will only have to be done once?
Thanks in advance,
This is a situation that I have encountered many times in my years of designing databases and there is only really one solution.
I will explain. If you email me at firstname.lastname@example.org, I will send you a mock-up I have created in Access of your specific problem.
What you need is two tables to start with - one for the businesses and one for the categories. (If there was only one category per business, you could do it all in one table of course.)
Now for the fun part. The first and obvious step is to link these two tables together so that a relationship can be built between each business and the related category. Using only these two tables, you would still only be able to link one category to one business since you would be required to record the category on the business table in any event.
What is needed is an "intelligent" link or relationship between the two tables to resolve the Many-To-Many problem you have at the moment (Each category can be linked to many businesses and each business can be linked to many categories.)
This link is established with the creation of a third table - a link table if you like. Let us call it BusinessCategory. On this file will be a business code and a category code linking you to the Business and Category tables respectively.
Let's consider your example:
As you can see, the combinations are not limited at all and the integirty of the data is maintained.
- On the Business Table you will have (among others) a record with a business code of "Business A".
- On the Categories Table you will have (among others) a record for category code "5", another for category code "10" and another for category code "16".
- On the BusinessCategory Table you will have (among others) a record with business code "Business A" and category code "5". You will also have a record for business code "Business A" and category code "10". Finaly, you will also have a record with a business code of "Business A" and a category code of "16"
Not only can you use this tabel to determine all the categories a business belongs to, but also all the businesses associated with a particular category.
I hope that this is simple enough - sorry if I have gone on a bit!
Please let me know if you have any questions.
I knew there would have to be a third table, I just didn't know what to do with it! I will email you.