Help with database design

[j_cocker]

[FONT=&quot]I am a MS Access novice, and am looking to develop an application based on Access 2007/10:[/FONT]

[FONT=&quot]- It is meant for a government agency that is tasked with assessing clients needs for certain equipment.[/FONT]
[FONT=&quot]- The agency sends an assessor to meet the client and produce a report highlighting the equipment the client requires.[/FONT]
[FONT=&quot]- The equipment is divided into several groups according to type.[/FONT]
[FONT=&quot]- Prior to purchasing the equipment for the client the agency is required to obtain 3 (or possibly) more quote from a list approved suppliers. [/FONT]
[FONT=&quot]- The quotes are typically obtained from the suppliers' catalogues or over the phone/email.[/FONT]
[FONT=&quot]- The equipment is then purchased within a week or so, so the quote from the supplier is essentially only valid for a week and needs to be kept for record-keeping only (not for future use).
[/FONT]

[FONT=&quot]A diagram of the relationship between the different tables is attached. (the table tblAssEqptJoin holds the details of a quote from a supplier for a piece of equipment).[/FONT]

[FONT=&quot]Before I continue I would be most grateful for any corrections/suggestions/etc from the wiser/more experienced members of the forum.[/FONT]

[FONT=&quot]Many thanks in advance,[/FONT]

[FONT=&quot]J.[/FONT]

 

[tehNellie]

Don't call all your PK/ID columns "ID" you'll tie yourself up in knots at some point trying to figure out which ID you're referring to. Call tblClient.ID ClientID (or Client_ID, ether is fine, just be consistent) as you've done as the foreign key and call the FK column the same as the PK column it relates to.

I'd personally split the addresses into separate columns ie something along the lines of.
Street_number
Street
Town
County
PostCode.

I like all my tables to have their own inorganic [Primary] Key, ie tblEqtSupplierJoin. A Compound primary key on eqpt_ID and Supplier_ID would be valid though.

Enforce Referential integrity on all your relationships (Assessment to Client for example appears not be enforced at the moment).

Clients has a middlename field, assessors doesn't. Again this is preference but I like to be consistent, if one name uses a middle name/initial then I like all my names to use the same convention.


Supplier_ID in the tblAssEqptJoin table concerns me. There is scope for an entirely Invalid supplier to be recorded there. I'd be more inclined to replace that and the equipment_ID with a relationship to the tblEqptSupplierJoin table which then gives you both pieces of data via one FK instead of two and ensures you can't record an invalid Equipment/Supplier partnership.

 

[j_cocker]

Don't call all your PK/ID columns "ID" you'll tie yourself up in knots at some point trying to figure out which ID you're referring to. Call tblClient.ID ClientID (or Client_ID, ether is fine, just be consistent) as you've done as the foreign key and call the FK column the same as the PK column it relates to.

Makes sense - I'll change that.

I'd personally split the addresses into separate columns ie something along the lines of.
Street_number
Street
Town
County
PostCode.

Was/am going to do that (+add many more fields to some of the tables - was just trying to get the relationships and structure correct at this stage).

Enforce Referential integrity on all your relationships (Assessment to Client for example appears not be enforced at the moment).

Missed that one - thanks.

Clients has a middlename field, assessors doesn't. Again this is preference but I like to be consistent, if one name uses a middle name/initial then I like all my names to use the same convention.

Agree - see my comment above.

Supplier_ID in the tblAssEqptJoin table concerns me. There is scope for an entirely Invalid supplier to be recorded there. I'd be more inclined to replace that and the equipment_ID with a relationship to the tblEqptSupplierJoin table which then gives you both pieces of data via one FK instead of two and ensures you can't record an invalid Equipment/Supplier partnership.

This is where I am/was really struggling...

- Every assessment is associated with 1 or more pieces of equipment.
- Every piece of equipment should be associated with a 1 or more quote (inc supplier, price, date, etc)

How do I create the correct relationship for this? I am really puzzled...

 

[tehNellie]

This is where I am/was really struggling...

- Every assessment is associated with 1 or more pieces of equipment.
- Every piece of equipment should be associated with a 1 or more quote (inc supplier, price, date, etc)

How do I create the correct relationship for this? I am really puzzled...

Look at your existing structure, that a type of equipment might come from more than one supplier is catered for by your tblEqptSupplierJoin Table.

it tells you that you can buy a Drill from B&Q or Dave's DIY. What it doesn't do is tell you that This Drill came from B&Q

Is tblAsseEptJoin essentially the same as a Quote? (it has a Price column in it so while it is technically a Junction table, it also holds pertinent information in its Own right).

Ie. 1 Assessment looks at many "quotes", each quote is for one type of equipment supplied by a particular supplier.

In which case a relationship from tblAssEqptJoin to EqptSupplierJoin tells you that the tblAssEqptJoin/Quote information refers to a Drill, or number of drills, supplied by B&Q

Does that make sense?

 

[j_cocker]

it tells you that you can buy a Drill from B&Q or Dave's DIY. What it doesn't do is tell you that This Drill came from B&Q

Is tblAsseEptJoin essentially the same as a Quote? (it has a Price column in it so while it is technically a Junction table, it also holds pertinent information in its Own right).

Yes - it is a quote.

Ie. 1 Assessment looks at many "quotes", each quote is for one type of equipment supplied by a particular supplier.

In which case a relationship from tblAssEqptJoin to EqptSupplierJoin tells you that the tblAssEqptJoin/Quote information refers to a Drill, or number of drills, supplied by B&Q

Does that make sense?

Yep, it makes sense and should work, but it is not as "clean" a solution as I was hoping for... My problem is that I was hoping that an assessment will point to (one or more) piece(s) of equipment, and they in turn will point to quotes for them.

Using your solution will make it a lot less instinctive to, for example, extract the list of equipment that a specific assessment is associated with.
Can this be done? I cannot see how?

 

[tehNellie]

Yes it can, I'm trying not to fling massive amounts of changes at you until I get a better idea of what is going on (and you wrap your head around some of the changes).

So, start to break down what it is that you actually need.

So you have an Assessment. Each assessment has a "shopping list" of Types of Equipment and presumably quantities required?
At this point you don't care who can provide that Drill we mentioned before, just that this assessment that you're dealing with wants one/two/a thousand of them?

Once you've defined the "shopping list" you need to get quotes from the suppliers who can provide that/those pieces of equipment?

So can we safely say that we could do with a tblShoppingList? (you can call it something more appropriate)

shoppingList_ID
Assessment_ID
Equipment_ID
Quantity

Now we have a means to see exactly what equipment and the quantity, an assessment requires. 1 assessment can have many items in the shoppinglist Table. 1 type of equipment can appear many times in the shopping list table.

So what we now need to do is to get quotes from suppliers who can provide that equipment. you can find out who supplies that equipment by querying your tbleqptSupplierJoin Table. But we need somewhere to hold the quote details as they arrive:

tblQuotes
-----------
Quote_ID
ShoppingList_ID
Supplier_ID
DateReceived
Price

Each item in the shopping list can have many quotes recorded against it. A Supplier can submit many quotes against the same item in the Shopping list.

It's a basic layout and it might need some refining but your assessment can now have many types of equipment recorded against it, and each equipment type can have many quotes recorded.

There is still the possbility here, for example, that a supplier who, according to your eqptsupplierjoin table doesn't sell drills has apparently provided a quote for one.

 

[j_cocker]

Yes it can, I'm trying not to fling massive amounts of changes at you until I get a better idea of what is going on (and you wrap your head around some of the changes).

So, start to break down what it is that you actually need.

So you have an Assessment. Each assessment has a "shopping list" of Types of Equipment and presumably quantities required?
At this point you don't care who can provide that Drill we mentioned before, just that this assessment that you're dealing with wants one/two/a thousand of them?

Once you've defined the "shopping list" you need to get quotes from the suppliers who can provide that/those pieces of equipment?

So can we safely say that we could do with a tblShoppingList? (you can call it something more appropriate)

shoppingList_ID
Assessment_ID
Equipment_ID
Quantity

Now we have a means to see exactly what equipment and the quantity, an assessment requires. 1 assessment can have many items in the shoppinglist Table. 1 type of equipment can appear many times in the shopping list table.

So what we now need to do is to get quotes from suppliers who can provide that equipment. you can find out who supplies that equipment by querying your tbleqptSupplierJoin Table. But we need somewhere to hold the quote details as they arrive:

tblQuotes
-----------
Quote_ID
ShoppingList_ID
Supplier_ID
DateReceived
Price

Each item in the shopping list can have many quotes recorded against it. A Supplier can submit many quotes against the same item in the Shopping list.

It's a basic layout and it might need some refining but your assessment can now have many types of equipment recorded against it, and each equipment type can have many quotes recorded.

There is still the possbility here, for example, that a supplier who, according to your eqptsupplierjoin table doesn't sell drills has apparently provided a quote for one.

Thanks a lot! That looks much tidier.

I attached the latest version of the database diagram below. I will add various fields to the tables, but does the structure look correct now?

 

[j_cocker]

Anyone?

Thanks.