Complex Table Layout Help

srmousse

Registered User.
Local time
Yesterday, 16:28
Joined
Jun 27, 2005
Messages
16
I am working on a complex database to store Business information as well as specific into about that Business... here is the tricky part...

The individual companies are sorted by Business type (i.e. Computer Software Companies, Child Care Providers, Commercial Building Contractors, etc.) and each Business type needs to hold a simi-customized set of information (i.e. Commercial Building Contractors needs Total AZ Billings for Commercial Bldg., $ Awarded for Completed Contracts, % of Work Subcontracted, No. of Local Employees, and Areas of Specialty). I will refer to each type of information as a Category. Each Business Type has between 4 and 7 Categories. Within a Business Type there are between 10 and 30 companies listed. There will be just over 200 Business Types listed within my database structure, as well as around 100 unique types of Categories.

Primarily, I need to be able to query and format each Business Type, to include the Companies information as well as each Category that is associated with the Business in a flat spreadsheet style layout.

Now, I intend to create a unique report (if needed) for each Business Type as needed to customize the look (i.e. column widths and any other special formatting needed) of each Business Type,

Here is where I stand currently with this project…

I have 4 tables,
1. Business (it includes basic contact info that is common to all business)
2. Category (is basically a list of all possible categories)
3. BusinessCategory (a Many-to-Many relationship container that links the BusinessID to the CategoryID and includes the corresponding Value)
4. BusinessType (a list of business types that a business can belong to)

This structure seems to work in that it can handle the customization of categories for each Business within a BusinessType. The problem I am running into with this structure is creating a query to handle the data and create a “flat” table for a BusinessType with each Business and it’s categories.

It may be that I can use this structure to do what I need done, however I think that there must be a better way to structure my data to work the way I need it to work. I really appreciate your input on this!
 
Hi

Can you confirm you want the one business type per business? Also, does each business have only one category or many categories? Lastly, are the categories restricted by the choice of business type?

Andrew :)
 
There are between 7 and 36 businesses per business type... there is only one business type per business.

There are 3-8 categories per business type (which translates to 3-8 categories per business), all businesses within a business type use the same categories. Some of the categories are common with some other business types, however there is no single category that is common to all business types.

Finally, the categories for a business are determined by it's business type.

I hope this helps! I feel like I have created a riddle, I guess in a way, I have.

Thanks so much for your help!
 

Users who are viewing this thread

Back
Top Bottom