Table Relationships

[orange]

I think there are some tables that could be used as lookup/reference tables. These may not have to be in your conceptual model individually, but could be lumped under Reference Tables (perhaps).



It would be helpful to readers -and to you - to have some definition/description of BuildId and ProductionID to help in understanding the "business".
For example, what is the relationship between Dealer and Branch? And between Warranty Claim and Branch?
We could also use some examples of Product Code and Category.
You have a tblProductType, but it doesn't contain ProductType specifically. It has ProductID, CategoryID, ProductName and ProductCode?

Your OrderDetails table should have a field called AgreedToPrice or PriceAtSale---do not depend on the Price in the Product table to be constant. It will change with time, and you could lose historical data. I would not include a time component in the representation of OrderDate.

Have you tried taken an aspect of the business ---say Warranty Claims and model that separately? And Customer Order process?

Without having some description of your business and business rules you can not vet/validate your model.

What does tblSuffolk represent? And its purpose?
What about tblTrailersOut???

You have done a lot of work to create this data model. The question is Does it represent your business?

Good luck with your project.

[Beanie_d83]

There may be a few tables that are now redundant, these were just for experimental purposes so I still need to do a bit of housekeeping to tidy it up.

When a trailer is ordered it is assigned a production number for factory reference (much like a cars vin plate). At the same time it will also be given a serial number (I don't know why they need both but the higher powers in this company seem to think it necessary).

In a nutshell, I mainly deal with the design of our products but I am also tasked with warranty/guarantee and I want to produce the database that helps me keep track of these. To do this I need to know what the specification of the trailer/machine is, who it was sold to (a machine will initially be sold through one of our dealerships, then on to a customer). Upon taking delivery of the machine, the dealership is required to inspect the machine to see if it meets the order for which we supply a checklist sheet. On said checklist the dealer fills out the customer details (name/trading name, address, etc) which is returned to us and registers it for the guarantee. At the moment all this information is scanned onto our server and saved in a folder under the production number.

On top of the above I will require information about what aspect of the machine has failed, cause of failure (if known), origin of the part (was it bought in from another supplier or did we produce it) and so on.....

[orange]

You have done a lot of good work. I agree that a little tidying may be in order, and you can review your current structure with some sample data to refine things. Also, you may want to break out some of the major business functions and review the details of that part of your model. I'm thinking of Customer Orders, Warranty Processing...
Do not underestimate the importance of clear documentation, thorough testing and communications with others who will be expected to use the eventual system. Get others involved, it will help with refining/resolving/clarifying any uncertainty.

Good luck with your project.

[Beanie_d83]

As I am trying to reduce the number of indexes I have, when using a table field as a lookup/reference should there be a relationship link (one-many) between this and the table being populated with its data? I don't think there should be as this then surely counts as an index, right? I did check the referential integrity box, but what is the relationship if RI isn't checked...one to one perhaps?

[orange]

Please provide some sample data to help with this

when using a table field as a lookup/reference should there be a relationship link (one-many) between this and the table being populated with its data?

You want lookups between tables via forms. Not lookups at the table field level.

[Beanie_d83]

So this isn't right...

Relationship_Lookup.pdf

[orange]

It seems fine to me. It is often easier to see the usage by giving some sample data.

This is what I was warning about --- table lookups at the table field level.

[Beanie_d83]

It would appear that I have gone and done just that. I don't have a table called lut_Dealer, only what I created using the lookup wizard. I really need to break this habit!

[orange]

OK, so lut_Dealer wasn't simple a Long Integer FK to DealerID in tbl_Dealer.
Thar was my intent, and how I would set up the linkage.