SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Mar 2002
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,
    Sydney

  2. #2
    SitePoint Zealot thespian's Avatar
    Join Date
    Sep 2000
    Location
    South Africa
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 thespian@acenet.co.za, 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:
    • 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"
    As you can see, the combinations are not limited at all and the integirty of the data is maintained.

    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.
    Last edited by thespian; Mar 27, 2002 at 04:57.
    Bill Conté [Protected by Psalm 91]
    Web Mechanix
    Growing OLD is Mandatory - Growing UP is Optional!

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Nice write up thespian!
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    SitePoint Member
    Join Date
    Mar 2002
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I knew there would have to be a third table, I just didn't know what to do with it! I will email you.

    Thanks,
    sydney


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •