Table Structure (1 Viewer)

Hi,

its been a while since i have been anywhere near Access but i find myself in need of a structure and i cant think of the best way. I am hoping for a bit of help

I need to create a table(s) with a set of queries to filter down a mass list of products that will result in one final result. Example-

Range of Boilers
Range of Controllers
Controller Types
Type Method
Solution

Each Boiler can have many types of controllers. Select a controller
Each Controller has 3 control types. Select a Type
Each Type has 3 methods. Select a method
Solution is displayed.

i was thinking about storing a certain amount of re-usable information into a separate table and linking with an ID. that part is ok, i just cant think about the main structure. Should i use a single table with many fields or should i split it up into smaller tables?

Can anyone give a bit of guidance please?



Thanks

Nigel

What is your primary purpose? Do you want to store specification information about these parts, or are you primarily concerned with how the parts might be composed together in a finished product?

I could see how you might have different tables for boilers and controllers, since these objects will have very different specifications, but if your purpose is to store what controllers can possibly be used with what boilers, then detailed specifications may not be important to you.

For the latter problem I would have one parts table. Then I would have a composition table, that would look like . . .

tblComposition
CompositionID (PK)
ParentPartID (FK)
ChildPartID (FK)
Quantity

Click to expand...

. . . which effectively gives you a many-to-many relationship between parts, and then you can map what parts could possibly belong to other parts.

Hi

Thsnks for the reply. The database is more like a fault finding or solution database.cthe idea is, you select a boiler and only the controllers that work with that boiler will appear. From the list of controllers, you pick the one you need and only the controller types for tht controller appear. You pick the controller type and the solution is, a text description of a set up and an image.

Its filtering down the requirements. It wont serve any other purpose and all of the data will be input manually

Thanks

Nige

Yeah, so like what I addressed in the 2nd part of my post? You'll have one table of parts, and one table that maps the possible relationships between those parts.