Multivariate Field Relationships (linking one field to several other tables)

[DoktorNumbers]

Hello!

I'm relatively new to Access, and I'm having trouble designing a database and even knowing the right terms to look for in order to find a solution. I'm trying to design a database where one field - let's say "Products" - is linked to multiple others. In this example, I have a product list that I'll put into a table:

ProductID
Product 1
Product 2
Product 3
....
Product 10

Each Product has an assigned Product Manager. So I get:

ProductID ProductManager
Product 1 Manager 1
Product 2 Manager 2
...etc.

Each Product Manager, though, is responsible for multiple products. So the main "Product Management" table would be:

ProductID ProductManager
Product 1 Manager 1
Product 2 Manager 1
Product 3 Manager 1
Product 4 Manager 2
Product 5 Manager 2
Product 6 Manager 2
Product 7 Manager 3
Product 8 Manager 3
Product 9 Manager 3
Product 10 Manager 3


But each manager has their own objectives for each product, which I would also like to track. So, for example, I want to make a table for each manager and allow them to input different objectives for each of their products, and since the ProductID is a unique identifier, I would hope I could link these all together. For example, I want another table called ProductManager1 that looks like:

ProductID ObjectiveID
Product 1 Objective 1_1
Product 1 Objective 1_2
Product 2 Objective 2_1
Product 3 Objective 3_1
Product 3 Objective 3_2

For this table, the ObjectiveID would need to be the unique identifier (I think). The idea is that each Product Manager can then update the objectives for their assigned products, allowing me to query the main table at any point to get a snapshot of the objectives. So the desired query result is something like:

ProductID ProductManager Objective
Product 1 Manager 1 Objective 1_1
Product 1 Manager 1 Objective 1_2
Product 2 Manager 1 Objective 2_1
Product 3 Manager 1 Objective 3_1
Product 3 Manager 1 Objective 3_2
Product 4 Manager 2 Objective 4_1
Product 4 Manager 2 Objective 4_2
....etc.

I've tried doing this a few different ways, but I keep stumbling over how to create a relationship between the ProductIDs and the Product Managers. I realized at some point that the way I was defining the relationship was inconsistent, since I was essentially telling the database that the ProductID field was the same every table, but that's not strictly true: Each Product Manager's table on has a subset of the full list of ProductIDs.



So, that's where I am now. I don't even know if I have the right language to ask the question in a more succinct way, hence the long example

Thanks in advance for any help!

[Minty]

Take a step back, and describe (in laymans terms from 10,000 feet) your business model.

It sounds like you may need some junction tables, but lets get the bigger picture first?

[DoktorNumbers]

It's not about the specifics of the business - the "Product" and "Product Manager" variables are placeholders. This is about as general as I can make it! Each product has a manager, each manager sets individual objectives for the products for which they are responsible, and I'd like to create a database that allows me to combine all of this information into a single table so that I can then generate progress reports. But the first step for me is to figure out how to define the relationships between these variables/tables.

Hope this makes sense!

[Micron]

I'd like to create a database that allows me to combine all of this information into a single table

That's basically a spreadsheet. Might as well use Excel if that's a requirement. This is all wrong for a relational db, I'm afraid:
Product 1 Objective 1_1
Product 1 Objective 1_2
Product 2 Objective 2_1
Product 3 Objective 3_1
Product 3 Objective 3_2

That is 2 tables at least. Maybe this will help guide you should you stay with using Access:
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

[DoktorNumbers]

That's basically a spreadsheet. Might as well use Excel if that's a requirement. This is all wrong for a relational db, I'm afraid:
Product 1 Objective 1_1
Product 1 Objective 1_2
Product 2 Objective 2_1
Product 3 Objective 3_1
Product 3 Objective 3_2

That is 2 tables at least. Maybe this will help guide you should you stay with using Access:
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

Thanks for this. I've gone through the normalization articles, and they are quite helpful.

Excel is too clunky for this. Imagine you have 12 different product managers (to keep with the terms from the example) and each one has 10 products they are responsible for. Doing this in Excel leads to all the data redundancy that the normalization articles discuss. Moreover, the reason I want to use a relational database is to allow decentralized updating from each manager. Doing this in Excel means creating a bunch of VLOOKUPs that still won't capture all of the data in a single table, since each product manager has multiple objectives for each product.

To your last point about it being "all wrong" for a relational database, how would I need to organize the data to make it right? The main object of the database is the product, and everything else should link back to that initial product. That's why I thought doing it this way would actually make it easier to look at.

[pbaldy]

Post 5 was moderated, I'm posting to trigger email notifications.

[Minty]

You haven't really helped to describe the problem you are trying to model, with any details.
What are the managers objectives for a product, are there sales targets, production schedules, colour choices, number of clients etc. etc. ?
All of these things could be related to one either or both the manager or product.

Given what you have described you would need minimum of
A tblManager holding their details.
A tblProducts for all the product related data.
A ProductManager Junction table that would take the managers ID and the product ID Primary keys


But I suspect without further background this may need a lot of refining/ additional data / tables.

[Micron]

I want to use a relational database

I'd like to create a database that allows me to combine all of this information into a single table

Those 2 statements are at odds with each other. I went with the latter when suggesting Excel since it was posted after. I copied your table idea but considered that to be a main table I and thought some of the examples were progressions of the same table(s). Perhaps what I quoted was a junction table? That would be a different story. Not seeing a manager table kind of led me in that direction. RE:

I want to make a table for each manager

Don't do that. Maybe what is confusing you at this point is that notion. Based on my current (and perhaps flawed) understanding, you need
tblManagers
tblProducts
tblObjectives

IF objectives are linked to products AND 1 product can only have 1 objective AND only 1 manager is responsible for a product then
tblProducts can have
- tblObjectives.ObjectiveIDfk (could also put Objective field in tblProducts but less flexible)
- tblManagers.ManagerIDfk
So that's 3 tables. If multiple products can have multiple objectives (many to many) or if multiple managers are responsible for multiple products then you need junction tables instead.

As Minty said, the picture is not clear.

This is about as general as I can make it!

Generalities are not that helpful with table design issues.

[DoktorNumbers]

Maybe what is confusing you at this point is that notion. Based on my current (and perhaps flawed) understanding, you need
tblManagers
tblProducts
tblObjectives

This is an interesting way to think about it. I might try it this way, though in the actual data I'm working with there are like 300 "Products" with around 10 managers who are each responsible for around 30 products. My hope was to relate the data in such a way that each manager has their own table to update rather than go through the full list. I suppose they could just filter it themselves, though. But then your point about why I need a relational database to begin with is a good one.

[QUOTE=Micron;503535
IF objectives are linked to products AND 1 product can only have 1 objective AND only 1 manager is responsible for a product then
tblProducts can have
- tblObjectives.ObjectiveIDfk (could also put Objective field in tblProducts but less flexible)
- tblManagers.ManagerIDfk
So that's 3 tables. If multiple products can have multiple objectives (many to many) or if multiple managers are responsible for multiple products then you need junction tables instead.[/QUOTE]

The many-to-many version is correct, though it is also true that each product only has one manager. I'm not too familiar with junction tables, though.

As Minty said, the picture is not clear. Generalities are not that helpful with table design issues.

What other details are necessary? I have a portfolio of products. Each product has one manager. Each manager sets multiple objectives for each product and inputs their progress (some numeric value). Although these are not exactly the terms I'm using with my own data, they are close enough to create the same picture of what I'm trying to do.

[ArviLaanemets]

My hope was to relate the data in such a way that each manager has their own table to update rather than go through the full list

Managers must not have direct access to any tables at all! They will work through forms! And you can design forms in such way, that a manager can see only a list of products he/she is registered as manager only. Plus he/she can add new products he/she will manage from products table (of course this option can be reserved for his/her boss instead). And having a separate list of products for every manager in separate tables will be a future headache. Simply think about a case, where a manager must take over some products from another manager (and this will not be worst what may happen)!

You need a table of products
tblTroducts: ProductID, ProductName, ... Nb! A row for a product! This table must contain info, which defines the product, and preferably will not change over time. I.e. unless the sales price for this product is fixed forever, you can't have it in products table, or you lose all historical info. To keep info which can change over time, you need additional tables where such changes and change dates are logged in;

You need a table of managers
tblManagers: ManagerID, ... NB! A row for a manager! Again, any info about manager what must be displayed anywhere, and can change over time, must be in separate log tables (This is the reason I didn't include fore- and last names of manager here. It is for you to decide, it is good to have e.g. current family of manager, or must it be the previous one, in report for previous years, after she married lately);

You need a table where products managers are listed. And don't forget, you must have a single manager for product, but this doesn't mean this manager and product are linked for eternity!
tblProductManagers: ProductManagerID, ProductID, ManagerID, ValidFromDate, ValidToDate;

You need a table(s) for objectives - e.g. production orders, or sales orders, etc. Like
tblProdOrder: ProdOrderID, ProductID, ManagerID, ProdOrderDate, WorkstationID, MeasuringUnit, QuantityNeeded, ProductionTerm, ClientOrderNo, ...

[DoktorNumbers]

That's a great point. Thank you! I will look into the forms more closely - I think this may be a much better solution than the way I was trying to do it.

[Micron]

you can't have it in products table, or you lose all historical info. To keep info which can change over time, you need additional tables where such changes and change dates are logged in;

I'm not an accountant but I don't necessarily agree with that. Could also be that when a product is sold the current price x qty x discount rate = line total, and the current price comes from DLookup. The price at time of sale is therefore in the sales order line. Not saying that is better, just that there are other ways. Getting historical views of product pricing would be much easier the other way if you want to view price changes as historical records.
EDIT
@DoktorNumbers, I didn't specifically answer your last questions since ArviLaanemets did that pretty well.