More tables vs. More records

[Author]

My boss assigned me to a project. He sent an email to me that said "Put all
the Texas part numebrs into one table and all the Pennsylvania numbers into
another. Once we get the expanded spreadsheet from headquarters, we will
want to cross reference the two tables to make sure we didn't miss any. When
you make the F&O combined table, add a column that shows what family each
went to (SL20, etc)"
Now, if I follow his instructions to the letter, I'll run into the issue of
one part number belonging to more than one family. I'll then (I think) have
to do some awkward and redundant manual entry via a form or the datasheet
view of the table to make sure that Part 123 has its family field populated
by all the families it belongs to.
My question: Wouldn't it be easier to make a table for each family, and then
concatenate when he wants to look at "the big picture"? It sounds like a
good idea- I think I remember Crystal explaining this. Am I right?

 

[strive4peace]

Hi Author (what is your name?)

"make a table for each family"

no, make ONE table and add fields to the table to categorize the records
.... you absolutely should not have different tables for part numbers
depending on where they come from

Also, especially if you are going to be importing data, add these 2
tracking fields to your tables:

DateAdd, date, DefaultValue --> =Now()
DateEdit, date (use the form BeforeUpdate event to fill this)

"I'll run into the issue of

one part number belonging to more than one family."

then you would have something like this:

Parts
- PartID, autonumber

Families
- FamID, autonumber

PartFamilies
- PartFamID, autonumber
- PartID, Long, FK to Parts
- FamID, Long, FK to Families


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Jeff Boyce]

I agree completely with Crystal about using only ONE table, but I'm curious.

Is your boss making this specific assignment because s/he thinks s/he knows
"how" to do this, or because there's something inherent in the design s/he
is demanding that has use outside of the context?

For example, it may be that the table design proposed is being proposed to
make it easy to export the data... While this would be true for a
spreadsheet, it is irrelevant for a relational database. You can use a
query against a single table to extract whatever you need to have exported,
and that query will "look" like it is a separate table.

You might want to learn a bit more of the "why" before deciding the "how".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

So several parts can be in one family, and each part can be in more than
one family?

That is a 'many-to-many' situation. "Many" parts are joined to a family,
and "many" families are joined to a part, and "many" in this case means
just "possibly more than one"

Access is a database management system that makes easy things easy
to do. Unfortunately, many-to-many joins are not easy for Access.

The main problem is that if you have a table of parts, and a table
of families, and you join them to get part-family pairs or part-family
sets, you will have a non-updateable query.

For this reason, you may find that it is easier to do some awkward
and redundant data entry, rather than trying to get a perfect database
design.

(david)

 

[strive4peace]

"you will have a non-updateable query"

a form/subform should be used to enter data, not a query -- and then this:

"easier to do some awkward and redundant data entry"

is not necessary

~~~
thanks for adding your comments, David, I can tell you are good at
explaining things <smile>


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*