Table Relationships vs Queries (1 Viewer)

[psyc0tic1]

Ok... maybe I can explain my question.

My database as some of you will know is not normalized and I do not plan to fully normalize it due to it working perfectly as of now.

however today I added a couple more tables that I believe could be an effort in some normalization.

Start with one of my data entry forms (frm_newpartinput). This form is for adding Part number records to the database.

The form record source is the table (tbl_parts).
The first field (txt box number field) is called "PartNumber" (control source PartNumber field in tbl_parts) allows to put in a part number.
The second field (combobox) is called "Category" (control source Category field in tbl_parts) and has a query to populate choices from the parts table (tbl_categories) formerly was an embedded value list and tbl_categories didn't exist.
The third field (combobox) is called "Vendor" (control source Vendor field in tbl_parts) and has a query to populate choices from the vendor table (tbl_vendors) formerly was an embedded value list and tbl_vendors didn't exist.
There are a couple other fields but not the purpose of this question.

So... there are no relationships between tbl_parts, tbl_categories, or tbl_vendors. This is all done with queries.

Is this a bad way of doing this since it is just a data entry form?

 

[The_Doc_Man]

Running without relationships for things that actually ARE related in some logical way is always a dangerous way to do things. The problem is that if you don't define relationships for things then when you use a wizard, it can't help you. You have to do everything by hand and there, the danger is to misrepresent the relationships so that you end up with either a missed JOIN (i.e. things should be related but aren't) or a Cartesian JOIN - you get too many combinations of things.

It's like doing a trapeze act with no net. Doable but dangerous.

You say there are not relationships among the parts, categories, and vendors table, yet you jump through hoops to fill in those fields. Am I missing something... or are you?

 

[MajP]

So... there are no relationships between tbl_parts, tbl_categories, or tbl_vendors. This is all done with queries.
Is this a bad way of doing this since it is just a data entry form?

I would say this is fine for the most part. The real purpose of the relationship window has nothing to do with helping you build queries. Its an additional feature. The only time I ever create a relationship is to enforce referential integrity. If you are not enforcing it then there is no real purpose unless you really want a helper to make a join when you do a query.

Imagine I have a company database with an employee table and a children table. It makes no sense for have a child in my database if there is not a parent working there. I am also enforcing cascade deletes so if I delete the parent employee it deletes the children so I do not have any orphans.

Yes a part should have a category, but I probably still want to enter a part even if I do not know that there is a category. Just because a field is required does not mean I need a relationship. Also if the category table only has a category name and not other data you are not relating the tables only using a table for getting values.

Part number may be a different story. Seems to me that could have more related information and nothing should exist in a parts table without a part number.

The other nice thing about a relationship is cascades updates. If part id zxc1 is renamed to zxc2 and this is the pk in the part number table and the FK in the parts table. All the foreign keys will update

 

[psyc0tic1]

Yea... the Categories table and the Vendors tables are only used for looking up values to keep from typing them in different every time. I changed them from embedded value lists to avoid having to update them in the forms if a new category or new vendor was added.

I appreciate the feedback gentlemen.