Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    29

    Unanswered: Ideal setup of tables for many categories and many sources

    My scenario:

    I need to store information for 5 Categories, but will also have 200 Sources. Each unique category will have its own set of fields. Each source will use at least 1 category, up to 5 total, and any combination in between. Each source could record a low amount of entries per day, to extremely high entries per day.

    I know that I at least need to have 5 different tables, 1 for each Category.

    What I don't know, is what to do with the Sources. I'm thinking that there could be a single Source table, and in it would be three columns - an Index#, the Source name, and the Category name.

    The only problem is that these tables could get HUGE really quick. I'm wondering if in this scenario, it would be ideal to create 1 table per Source, and have a Category column in them (along with all the sub-Category columns). But then the additional problem is that I will be adding and removing Sources all the time, which means I will be frequently creating tables. If I ever wants to add/remove/change columns, I'd have to update every table.

    Is there another way to do this I'm not thinking of?

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    What's the nature of your information? From your description I take that your categories are static entities. Are your sources static to? And what defines the relationship between those two? From what I understand it doesn't seem ideal to me to create 1 table per source. I would think 1 table for all sources with a 'weak' entity to store the relationship with categories is the conventional way to do this. But I'm affraid I'm missing something.
    Last edited by Tyveleyn; 09-24-06 at 07:00.

  3. #3
    Join Date
    Feb 2004
    Posts
    29
    The categories should remain static.

    The sources will be somewhat dynamic - occasionally I'll need to add a new source, and less frequently discontinue a source (but left in the db for archiving).

    I think you're right, I should probably just have a single sources table.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    And maybe add a Source->category table.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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