Desperatly seeking someones opinion

[Samait]

Hello all! I have developed several databases but really seem to be drawing a blank here. My brain has frozen. I am trying to normalize a database i took over...

Ok...after reading everything here and trying to apply it appropraitely, i have come up with the tables i show in the attachment, but for ease of "database at a glance", here's what i have....

Tbl_Line
LineID (PK Auto Number)
Line (text stating name of process line)

Tbl_Base
BaseID (PK Auto Number)
baLineID (FK to Tbl_Line)
baRunDate (date indicating date of work)
baKey (field indicating key (aka workorder) number, which is not always available)
baProduct (Field indicating product, again, not always available..it's usually either key or product available, but they must be separate fields)
baShift (Indicates shift that did work)
baSide (indicates side of wood, not always available)

Tbl_Prod
ProdID (PK auto number)
prdBaseID (FK to Tbl_Base)
prdType (FK to tbl_PRDType)
prdAMT (indicate amt. produced)
***Note: There may be several types of production for one key or product

Tbl_PRDTYPE
prdTYPEID (PK Auto Number)
prdTYPE (type of production...indicates grading of wood product)

Tbl_Downfall
DwnflID (PD Auto Number)
dfBaseID (PK to Tbl_Base)
dfTYPE (Foreign key to Tbl_Dwnfl_Type)
dfAMT (Amount of downfall)
dfCAT (Foreign Key to Tbl_Cat)
***Note there may be several types of production for one key or product

Tbl_Dwnfl_Type
DwnflTypeID (PK Auto Number)
dftType (Type of downfall)

TBL_Dwnfl_Cat
CatID (PK Auto Number)
dcType (Downfall Categories)

ok...so my first question is, how is this? Ok? Then i will actually have a follow up question about transferring data in....LOL But first i'll check if this seems ok to you guys. I want to make sure that when i run queries on the data, i am not going to get duplicate data on the production amounts....although, i don't think i would, but as I state this, i have to wonder if production should be a part of the downfall table...like instead of downfall types i have listed, i call it something else and add production to the list...let me know.
Ok..my last question...i have to upload data into these tables, but because it is set up so differently, i'm not sure how i would do that. If you think you can help me, please let me know & I will send you my new & Old databases so you can view & help, if that is possible. If you simply know of a way to do it overall, let me know! THANK YOU SO MUCH!

 

[lameid]

First, what is downfall exactly? I'm not all that familiar with this industry.

Second what is Tbl_Base? It seems odd that you may have product there but it isn't always available but you would then in turn relate product to it? What am I missing?

 

[Samait]

Tbl_Base could be compared to a table similar to Tbl_Invoice. My vision on this is as follows...

The Tbl_Line holds the information regarding the names of the process lines. Wood is run through one of several process lines to come to a finished product. I have to report on the productivity/efficiency by line.

Tbl_Base holds the basic information regarding the run through the line such as the run date, the product and/or key (the reason one or the other is not available is that oftentimes one or the other is available. Here's the thing...we have a program called NaVision that does all of the accounting, inventory, etc... and that program holds the product information, etc.. Once i am done w/entering the information I need, I use linked tables to update the product number from the key when it wasn't originally indicated on paperwork). This table also holds information on what shift performed the work and what side of the wood had the downfall.

Tbl_Downfall...let me first explain downfall. Downfall is basically a grouping of the causes of "bad product". For example, a sheet of wood might go through the line & perhaps it has lines in it from the stain/finish/print that was done. Then the guys running the line would categorize it as "Ink Lines". There are about 50 specific categories that they fit into. When i developed Tbl_Downfall as it relates to Tbl_Base, i kind of looked at it as an invoice set up....tbl_base being the invoice header, with Tbl_Downfall and Tbl_Prod being the lines.

Does this make sense?

 

[lameid]

If you are going to update the product in table base and each base is for one product, I would keep the product in table base and not include it in related tables.

If on the otherhand you have a line that produces two or more products simultaneously (a reasonable expectation even if you are not doing it now), you will need to put it in the related table(s). If it is in the related tables, then I don't think it should be in the header or base table.

Perhaps you should be looking at two sets of detail... One for work order (as in make 12 widgets or make 12 good widgets; 12 inculdes downfall vs 12 produced) and a second for what was produced (downfall and what you call production).

Honestly, I would not separate downfall and production into two tables. I think for reporting it will be much easier to have everything in one table. Unless of course their is so much detail that you want to keep the size down by splitting in 2.

Tbl_Produce
ProduceID (PD Auto Number)
prBaseID (PK to Tbl_Base)
prQualityTYPE (Foreign key to Tbl_Quality_Type)
prAMT (Amount produced)
dfCAT (Foreign Key to Tbl_Cat)
***Note there may be several types of production for one key or product

Tbl_Quality_Type
QualityTypeID (PK Auto Number)
qltType (Type of quality; Good + Downfalls)
qltDownfall (Is this type downfall?)

In this way you assign a quality type and you define whether a quality type is downfall. It is intentionally denormalized from your tables which has some merits. On the otherhand you may want to stick to your method for other reasons. This is more of a gut call based on what the system will do than a right or a wrong.

 

[Samait]

I agree with you on the downfall & production being in one table. As I look at it, i realize that this would best service the database. Thank you so much for your input!

 

[fneily]

In my opinion, make all foreign keys spelled the same as their related primary keys. Get rid of autonumbers. Create your own primary key rules. What do your junction tables look like? You maybe able to transfer some info into them.

 

[Darrylles]

Hi,

In my opinion KEEP the difference between Primary and Foreign key names.

E.g.

tblMyTable tblOtherTable
pk <--| pk
fieldname | fieldname
|--->> MyTable_fk

A foreign key named the same as the related primary key tells you nothing about the table without searching all tables to find the key name.

A foreign key name that incorporates the related primary key name AND the related table name is great for future maintenance.

Also KEEP using autonumbers AND name the primary key the same in all tables also. This means that you must always qualify any reference to the key with the tablename e.g:
tblMyTable.pk

fneily, I do understand where you are coming from, but times have moved on from Codd's time. (Yes, 'efficient' database design relies on Codd's rules, but Codd didn't have 'autonumbers' then).

ATB

Darrylle ;-)





Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[fneily]

Darrylles
Nor would he want them. In his original relational database development theory, Codd also doesn't have foreign keys. In fact, in theory, there are no foreign keys. They're a necessity in reality. For neophytes in Access, naming the foreign keys the same as the primary keys will facilitate their query creations. They do not have to manually make the table connections.
Autonumbers have well documented quirks associated with them that again is confusing to a beginner. "'efficient' database design relies on Codd's rules, but Codd didn't have 'autonumbers' then". Please send me your raw data that shows autonumbers make databases more efficient. Not someone else's opinion, but the actual raw data. I've never seen that.

 

[Samait]

Thanks you guys! I appreciate all of this information! I'll take all of this and develop from there. I really really appreciate it!

 

[lameid]

I have to side with Darrylles on autonumbers. It is the single easiest way to provide a unique seed to a table within Access. Definitely the way to go for 'neophytes'.

As for fieldnames... It will work regardless of the names. I don't like putting table related information in field names generally. Usually, fieldnames are unique enough. There is value to adding it to fields like quantity and price that may be repeated throughout tables. So my foreign keys often are the same as the related primary key. However sometimes they are different as I might make the PK for my widgets table WidgetUID. UID means unique identity and hence does not belong in a FK name. This is my opinion. It leads me to use FQN's (Fully Qualified Names:= Tablename.fieldname) more.

 

[misscrf]

Just to add to the pile of bricks, I recommend taking some time to read this:

http://www.utteraccess.com/forums/s...=373096&page=0&view=collapsed&sb=7&o=&fpart=1

Make sure to read at least the links on 1 2 and 3. This post is what taught me normalization. Now that is never a problem for me.

Now to figure out how to make the code make that much sense!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Darrylles]

fneily,

This 'argument' of course can (and has) gone on forever.

I did not say that autonumbers made database design 'more efficient'.

If all database / software developers stuck to the same standard - all well and good but usually non-tech business managers define time-scales - not developers.

You ever tried to understand a complex database without schema / model documentation in a medium-sized company that refuses to accept the complexity of database / software design?

When I leave a company, I know that it will attempt to 'cut corners' regardless of my placing my job on the line in attempting to make the company see sense. I also know, that my table design / relationships are self-documented purely via the field names (if the schema docs are lost). i.e. You can make sense of table relationships without a documented schema with my method.

If you have worked with a company for 10-20 years, that accepts without argument what you are saying - great, and I'll agree with you. Try getting out there and changing jobs every year, if you do it your way, then you are simply creating new contracts for yourself in the future; because you will be the only person with knowledge of the system - that is unprofessional in my opinion (regardless of employer stupidity)).

ATB

(No hard feelings)
Darryle







Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk