Multiple relationships

[pinkgecko]

I need some help figuring out the best structure for a database I'm working on. Basically, it has three main types of data in each record: A name, which "category" that name falls under, and then a list of customizable attributes based on their category (set number of 5 attributes, unlimited categories). I'm coming from another language (using SQL) which compiles all this data into a single database, and I'm writing it all to a single table. I just need to figure out how to pull it out of the table, put the correct column headings on it, and then pop it into a report. An example would be:

First Column: Name
Second Column: Work Unit (examples: marketing, warehouse, etc)
Third Column: Attribute 1
Fourth Column: Attribute 2
etc.

Now, using this example again, Attribute 1 for marketing would be total sales, but Attribute 2 for warehouse would be total items stocked. But they would both be written to this main table under Attribute 1.

How do I tie this information together? I need a report, individual to the area, that will give the results for each attribute, along with the name. I would imagine I have to do a query and a report for each "category"; do I also need an extra table that defines the name of each attribute?

Or is there another way that would be better? I haven't really planned a complex database up to this point, so I don't always understand how to use the full capabilities of MS Access. thanks for your help!

 

[PHV]

Have a look here:

http://r937.com/relational.html

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[johnherman]

One solution (truly relational).

Master table
Name Key
Name
Work Unit
Work Unit Key

Marketing Unit Table
attributes that apply to Marketing

Warehouse Unit Table
attributes that apply to Warehouse.


Another solution (denormalized for performance)
Single Table
Name Key
Name
Work Unit
Total Sales (leave null if not applicable)
Total Items Stocked (leave null if not applicable)
etc.



-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[lespaul]

why wouldn't you just have a single ATTRIBUTES table with a flag to indicate which department the attribute applies to?

Attribute
ID
Name
Department




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[pinkgecko]

The problem with having "Total Sales" and "Total Items Stocked" in the main table, and leaving them null if necessary, is that I have to hard code it in my program that's writing to the table since it uses SQL. I have 100+ "work areas", so I'd also have at least 1000 columns in my main table - doesn't seem the best solution to me. That's why I wanted to stick with Attribute1, Attribute2, etc. instead of trying to have all possible columns in the main table, or even have a different table for each "Work area." My main problem is trying to figure out the relationship between the two tables, and on a report, joining(?) the tables so the records in "Marketing Unit Table" are used for column names in the "Marketing Unit Report" - when the stats are pulled from the main table. It's not a thing I've found in the relationships tutorials, because it isn't the cleanest database design, but I don't have too much choice in what the original table looks like.

 

[lespaul]

why would you need 1000+ columns in your main table?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design