Normalisation Help

[olorin]

I have a Table Structure that works fine, but I want to trim it down a little.
I know that a few tables aren't normalised properly.
Three of the tables (tblCustomer, tblSupplier, tblHaulier), could be trimmed down to two tables (tblCompany) with a link to (tlkpCompanyType).
This would then allow me to make just one "Contacts" table, etc etc.
My problem is, I do not know how to refer to both a Customer AND a Haulier in the tblShipments or the tblShipBookings, as both of these tables need to have the names of both the Customer AND haulier in them (or at least the foriegn key).
You can see a jpeg of the database relationships here
Any advice would be most welcome.
Thanx

 

[Banana]

Instead of having a Supplier Address tables, Supplier Contact tables, Customer Address table and Suppleir contact, which are basically the same; you only need to create something like this:

tblEntity
EntityID (FK to tblEntity)
CompanyType (chose between Shipper or Hauler or Customer in a related lookup table)
FirstName
LastName
BusinessName

tblAddress
EntityID (FK to tblEntity)
blah blah

tblContact
EntityID (FK to tblEntity
blah blah

That'll fix the problem of multiple Address/Contacts table and will help create a relationship between Customers, Haulers and Suppliers. Oh, you can see there's three "Names" field in tblEntity- You could condense to just two; FirstName and LastName and if it's a business, you can make FirstName required for Business' name and leave LastName blank, and require both for Customers. I only thought you might want to keep a contact person's name from all business in which three names would be helpful.

There are other issues with the tables, but I don't want to start as I'm not too sure I understand as the diagram is confusing. When you've fixed the Contact/Address table a bit and cleaned up the diagram, show us again to help you further along.

 

[olorin]

Thanx for your reply,
I have trimmed-down the tables to what I think is Proper normalization, but I still can't see how to relate to a Customer (Organization) AND a Haulier (Orgainization) in the Shipments table.
Updated jpeg of relationships here

 

[Banana]

You haven't changed the address and contacts table yet. As I said, you don't need address and contact table each for a shipper, hauler and customer. Consolidate shipper, hauler, and customer into a single table and use only one address and one contact table. Repeating data means no repeating table as well.

Once you've done that, we can work on how to relate customer to hauler.

 

[olorin]

I have
maybe you need to refresh the page.
I have One contact table and one address table, and One Organisation table

 

[Banana]

Well, that's what I get for not emptying my cache. Serves me right. Sorry.

Looks much better now.

Just an option: You don't really need to have a separate telephone and email table; what I did with my contact was something like this:

tblContact
PersonID (FK to the tblPerson) in your case, that's OrganizationID
ContactTypeID (FK to tblContactType)
Contact

tblContactType
ContactTypeID
ContactType (lists: Phone, Fax, Message, Mobile, E-mail)

and in my forms, I have it programmed to use input mask for telephone numbers and none for email depending on what contact type the user selects.

Maybe you have a good reason for keeping them separate, but wanted to give you a head up to different way of doing it.

Now to relate the hauler to customer; I see that you have a OrganizationType.

Therefore, when you make a form, you would base it on a query. For example if you want to look at customer's record, the form's query would select only those that has the organization type set as customer. Same thing for the supplier and hauler.

Now for the order form, you would base a combobox's row source on a query showing only haulers.

If you do need to relate haulers to customers (e.g. customer use only few select haulers or something like that and you need to know that information), you want to read up on self-join. Here's a good thread about the self join.

HTH.

 

[olorin]

Brilliant!
Thanx for the input. I now realise that basing forms on Queries gives me more flexibilty. Thanx.
I can filter the data using queries for forms so as to separate Hauliers Suppliers and Customers, but I still dont understand how I can have TWO Organization IDs on ONE record of the Shipment Table.
I need to be able to record the customer shipped to AND by which haulier.

Ahhh.
I think maybe instead of having FKs to tblOrganization for the customer shipped to and the haulier used, I could just change the field names in tblShipments to "Customer" and "Haulier". I could then base the entry form for shipments made, on tblShipments, and then have a combo box for the Customer field based on a query to just show customers, and one for the haulier field based on another query that just shows hauliers from the organisations table.

If that is what you mean, I am deeply grateful for making things a lot clearer.

 

[Banana]

I'm not sure if you realize that you don't save a record in other record but rather the keys?

For example on the form, user has inputed in

CustomerName | ShipperName | HaulerName

John Doe | Acme Shipping | Ace Hauling

and in the tblOrganization, you have

OrganizationID | OrganizationName | OrganizationTypeID

1 | John Doe | 1
2 | Acme Shipping | 2
3 | Ace Hauling | 3


and in the tblInvoice, you would see:

CustomerID | ShipperID | HaulerID

1 | 2 | 3


This is the normal and proper to save tables that are dependent on other tables; rather than holding the record itself, only hold the key to the record.

For the combobox, you can create a query to filter the organization to only those that has a ID=1 (Customer) or =2 (Shipper) or =3 (Hauler).

Did that clears things up?

 

[olorin]

Yes I got that.
That's why I couldn't figure out how to store the Primary Key of TWO Organisations in the ONE record of the Shipment Table, as I had the Customer and Haulier fields set as Foreign Keys that linked directly to the Organization Table. But by doing it the way you said, I can have the Customer and Haulier fields just as normal fields and use combo boxes on an entry form to refer to the Primary keys of both the Haulier and Customer by using Queries.
Thanx again.
Once I have set-up the new table relationships I will post it, and maybe you could let me know if I'm on the right track.
P.S.
The reason I wanted Phone AND Email Tables, is because some of the Customers/Suppliers/Hauliers have a number of phones/Emails, at least this way I can see which phone/email it refers to by having it linked like that.
Thanx for your time.

 

[Banana]

Oh, I now understand what you meant-

In this situation, when you are making a form and assmuing you got your queries right (speaking of which- be sure to read up on joins- it's not same thing as relationship but very important for creating queries that you can update your data to; search the forum or google for joins and updatable querires), all you would have to do is drag and drop three instance of OrganizationID field into the form. Set one's rowsource to query pulling only customers, another to another query looking up just shippers, and last one limited to the haulers.

You can have several instances of same fields on the same form as you like, and because the rowsources are different, it's impossible to select the same ID by accident (even if somehow a shipping company was your customer, you still can do this, actually).

Was that what you were stuck on?

 

[olorin]

You got it in a nutshell.
That's exactly what I meant.
It isn't easy trying to convey my intentions with just plain text on a plain background, without inflexions in the voice.
Anyway,
I have done a new table design and if you could find the time to look it over I would be most grateful. You may view it here.
The way I have it set-up now is that tblShipments and tblBookedShipments only refer to the tblOrganization ONCE to find the customer name and I was going to refer to the Haulier by a combo box based on a query filtering out suppliers and customers. I think that's what you meant.
No doubt I will be cruising these forums when I start building Queries.

 

[Banana]

Okay, looks good. Now, there seems to be repeating tables:

tblShipments and tblBookedShipments: They seems to be basically the same and should be only one table. What were you trying to do with Booked shipments?

tblProducts and tblComponents: They can also be consolidated. I'm not sure why you decided that it should be separated; the fieldnames are identical which seem to me that separation isn't warranted. Maybe you can explain what you want to do with those?

 

[olorin]

I need to be able to track (see) shipments booked with which haulier and to where and when etc. Sometimes the Haulier fails to deliver on time and I always email bookings to the haulier just to cover my ass with the boss. So that if a delivery fails to get there on time at least I have a record of when it was booked
The Shipments table is basically for me to do a shipping note to send with the shipment and it would also deduct the products from the current stocks as it was shipped.
Products are all Customer related, that is to say we do NOT make products for the open market, all products are made to order and customer-specific.
All products have various components and some use the same components that's why I did the Junction table, some components are supplied by different suppliers at different times, hence the other Junction table.
As I am very new to database concepts (and still reading various books on the subject), I wasn't sure how to separate the components from the products without the two different tables, same with the shipments.
Thanks for your time on this.

 

[Banana]

Seems to me that the tblShipment would only need few more fields:

FieldName | Data Type
Booked | Yes/No
BookEmailed | Yes/No
DateBooked | Date

You then can create a query to return all booked shipments from the tblShipment. No need for a second table.

For products and components, if you look at the tables, they have basically the same fields. It really should be one table. However to help you "build a product with components", you may want a table something like this:

Here, this structure allows a part to hold multiple part numbers, specify which organization uses this number and their own description (although if you only need one description, you can move it back to tblProduct), and has a lookup that classify the parts (whether it's a product, component, whatever).

Now to create a product with component, you run a query showing only components and make it the rowsource of "ComponentID" then enter the quantity required for that. This also will allow you to have several component to one product, with all records being in the tblProducts. This also would be capable of supporting product having components that needs their own components as well.

Cavaet Emporer! This is my first time I've attempted this and this is untested, so I could be totally off the base here. Be sure to go over to Mircosoft Office Template center and download some Assets Inventory database samples to see how they handle the products/components.

Hopefully someone else will chime in and give you more inputs about the product/components.

HTH.

 

[olorin]

Brilliant! Superb!
It is only with your help that I am now beginning to grasp the idea of using Queries instead of making "redundant" tables.
I see where you're coming from with the extra fields in the shipments table so as to be able to get rid of the "tblBookedShipments", and since you point out that I could use queries to filter the data for reports, it's now obvious. As before I hadn't really grasped the idea of Queries.
Your table suggestion for the Products/Components seems sound too, though each product in itself has a unique part number and can either be built using various components with their own part numbers, supplied by varying suppliers, or sometimes Products require NO components except packaging and pallets, (although I suppose they could be construed as components).
Also, some Products utilise the same components.
Though having studied your table design suggestion, I think that would work too.
I will definately try out the Product/Component design you have kindly suggested, and see if I can get it to do what I want with some sample data.

Someone once said to me that Databases are NOT primarily for entering data INTO. They are first and foremost for getting information OUT of.

Thanx ever so much for your time and input, it has helped me tremendously.

PS.
When I said that each Product has it's own unique part number, I haven't fell into the trap of allocating that as a Primary Key, ALL primary keys in ALL tables are AutoNumbers set by Access.

 

[Banana]

Glad to help.

Feel free to come back if theres any question.

WRT to your PS comments- read up on debate about natural vs. surrogate keys. Most of folks here, myself here, prefer to use surrogate keys (e.g. autonumbers) instead of natural keys (e.g. Part Number) which can be indexed anyway.

 

[olorin]

While studying your suggested table design further and reading your logic, I am afraid I'm missing the point about the "ComponentID" field.
Do you mean that ComponentID is just for Query purposes, or is it a foreign key?
I have worked out that I could now remove the tblStoredComponents and just have tblStoredProduct with the design you outlined, as I could filter it to Components using a query, when needed.
Sorry if I am becoming a pain.

 

[Banana]

As I said above, cavaet emporer-

This is an area I'm not too sure on. See, if your product only had one component, I'd say you would need a self join, which I already gave you link few posts back.

But I'm sure that it'll have more than one components, and since components has same data as a product would, makes no sense to have a separate table. The link I pointed to before gives a superb example of self joining to only one field, but not multiple instances of the same field. So my logic was to create a "Junction table" only that it's joining two same tables (e.g. tblProduct), with ComponentID being related to ProductID. The thing is, Access may allow multiple joins from one table to another, but not relationship and that was where I got stuck on. Though, my proposal still will enforce RI for ProductID, and you can ensure that no ComponentID will be orphaned by doing one of those:

1) Prevent user from deleting any components if any products depend on it (e.g. a query to check if it exists in the ComponentDependency table)

2) If a user want to delete a component or product, caution that all related records will be deleted (e.g. run a delete query that looks up all the dependent productID and delete them as well)

3) Simply do not allow users to delete any records; it must be done by an administrator manually. (e.g. you?)

4) ????


So, in a nutshell, both ProductID and ComponentID are related to ProductID (though Access will not let me to have both fields related to same key), and it is up to you to ensure that ComponentID returns only ProductID that has type set as Component or something like that and does not cause a circular reference (e.g. Part A needs Component A which needs Component B which is also Part A! ) or orphans.

Maybe if you googled more for self-joins you can find results on how to handle more than one instance of same field to join to.

HTH.

 

[olorin]

Ahhh.
I think I get your drift now.
I would make a query with two instances of the same table "tblComponent" and "tblComponent_1" and then create a join between ProductID and ComponentID.
With ComponentID being the RowSource.

 

[Banana]

What was that query going to be for?

Seems to me I may have confused you into getting it backward-

A product can have many components.

A component can be used by many products.

That's a many-many relationship.

The problem? Component and Product are in same table, since the data you need for both are same. Self-join works for one-many or one-one relationship.

The solution? Use tblComponentDependency to keep record of what is needed.

Let's say Part A is a flange with 8 holes. It will need 8 screws, 8 washers and 8 nuts. In the tblProduct they will be listed as:

ID | Name
1 Flange
2 Screw
3 Washer
4 Nut

Now look up the tblComponentDependency you would see

ID | ProductID | ComponentID | Quantity
1 | 1 | 2 | 8
2 | 1 | 3 | 8
3 | 1 | 4 | 8

Note how productID is used as "key" to ComponentID.

On the form for creating a product record, I would create a subform which is essentially a duplicate of the parent's form but is programmed to store the new productID as a componentID to the parent record. You also can add a combobox containing query of components as rowsource to find existing componentID (which is actually productID) and make it a component to this new product.

Again, I remind you this is a unfamiliar area so be sure to research this question more thoroughly. I'm fairly sure someone else has been in your shoes before and asked similar questions.

BTW, did you look at some inventory database samples from here or Mircosoft.com yet?