Normalization problems (1 Viewer)

[Treason]

I am pretty good in VBA, but normalizing databases is not my strong suit. I am missing a major piece of insight. Here's the situation. I am building a shipping db and after I create the relationships between my tables I fail to see how to add new records. I am assuming I would have to build forms to make it easy on the end user, because users would NEVER remember all the key field values. I thought I could just create a query with all the tables joined and then make it the recordset of a form and data entry would be simplified. I think it is failing because I am enforcing referencial integrity, but maybe it's my joins?

I am getting a headache, lol

Here's a pic of my ERD

What am I missing here?


TIA

 

[gemma-the-husky]

you aren't missing anything

the client is the basis of your 1 to many records - so you can have a client that doesn't have any links (yet). you can also have a messenger.

however you can't have a location or a contact without a valid clientid, because this would break the referential integrity. and more importantly you can't have a delivery without BOTH a valid client and a valid messenger

this may all be what youwanted to achieve, but if not you may need to rethink your table design.

eg -
your table design of allowing a client to have many contacts and many locations may or may not be reasonable. Generally a lot of this stuff is included with the other client information in the client file, as most clients would only have one address, phone number etc. A lot of posters here do recommend a separate table of contact addresses etc for general use, but then you wouldn't be able (or want) to enforce the referential integrity in the way you have done.

hope this helps

 

[Treason]

Thanks gemma,

This is actually a database redesign. The original had all this info in one table. The user wants it to act like it did before, showing all fields together on 1 screen. Once I create the query with all tables joined, the recordset is not updatable, so how do I make it easy for the enduser? Must it all be done with forms and subforms etc, or can I just use one big query (ideal) ?

TIA

 

[OllyK]

I'd have at least 2 forms, keep the Client / Contact creation separate from the delivery creation, the former is likely to far more static. A combo on the delivery form will allow the selection of the Client and can then populate label(s) with the client details displayed. You can also design the form so that if the client doesn't exist in the combo, the client creation form is displayed so a new one can be added.