Normalizing Data - Just help me talk it through

[hrenee]

I sorta screwed up. I was given this database 8 weeks ago and I knew it wasn't normalized. I normalized what I deemed important but left one table un-normalized because thinking about it made my head hurt.



I have a tblArea - Which lists 9 areas with a description it is in a 1 to many relationship with

TblDepartment - Which has a list of 50 or so department, description and then the Area it is assigned to

The problem is the person put multiple areas in the ARea field on the tblDepartment. That's because things like Warehouse, happen in multiple areas (4 to be exact).

I was thinking the easiest solution to this is to just create a new Department with PK like PWARE (for peanut warehouse).


But I'm wondering if I'm missing something stupid, that would make this better and easier to use.

Thank you in advance

[ItsMe]

The problem is the person put multiple areas in the ARea field on the tblDepartment.

How is this possible? I understand there can be many areas related to a department. How can you "put" multiple areas into the Area field? In my imagination I see an Area field in the tblDepartment as an FK to a table for Areas. Seems like maybe you are coming up against a many to many.

[hrenee]

I hope you can see that. In description of the relationship it actually says indeterminate. Here is a screen shot of the actual table.


I'm hoping this end up bigger...

[ItsMe]

It looks like you have text fields for FK's. PK's and FK's should be number data types. If you don't imagine ever reaching more than 2 million records, the PK should be AutoNumber and the FK should be Long Integer.

The relationships window screenshot does not really shed any light on the subject for me. I see relationships, but don't understand the matrix or how this enforces business rules.

I suggest building a blank data base and starting with only two tables. You could import the table that represents the Areas and a table that represents the Activity that needs to illustrate the related area. I am not convinced this "Activity" is the "Department" table. I understand departments will work with many areas. However, drawing a relationship to the department from the area does not seem feasible.

If there is an event that takes place you can store the dept ID and the Area ID in this "Activities" table. Each event that takes place will be its own record in the Activities table. The simple fact that both ID's appear in the same record/row will illustrate which department is responsible for the activity and which area the activity took place in.

After you build a query in your blank data base and add a few records directly into the query, you may then understand how to place additional FK's, such as the deptID. You can then add more and more tables and more and more FK's

[Dal Jeanis]

Depends on how you want to use the data.

I'd suggest, as a first approximation, that you create a new junction table to hold the data that is now in the multivalue Area field. (That's really how Access is storing it, but the junction table is currently hidden from you.)

Create the table with all the info currently there, then delete the area field out of the Department table. Now you're normalized.

[hrenee]

Let me see if I understand. basically because a Department can be in more than one area, they really don't create something that should be separated this way, there should be a third table that brings them together in some way?

Sorry if I'm way off. What this is supposed to do is take a list of our fixed assets that was created (with Fair market values) by an independent auditor and then compare it to our corporate fixed asset list, and match them where applicable.

So I take my list of all assets in let's say RTE (which is a department of Peanut), take pictures of them, then attempt to find that fixed asset listed on our corporate asset list. Because we are talking about 1,000s of fixed assets, It's imperative that I can make my lists smaller to just do sections at a time. Even just doing say Peanut is too big. And then sometimes my boss wants to know everything in Peanut that's been found.

I'm a little lost on how to do this without the set-up that's currently there. But that probably just shows my lack of knowledge.

[hrenee]

Create the table with all the info currently there, then delete the area field out of the Department table. Now you're normalized.

Umm (I know how dumb this is going to make me sound), but which info. . .

[ItsMe]

I'm a little lost on how to do this without the set-up that's currently there. But that probably just shows my lack of knowledge.

I can appreciate that. But, taking some steps back and starting with a relative clean slate may force you to consider the business rules. Dal, is hinting at the same thing. You will most likely need another table. It will be difficult to imagine this table. If you start from scratch and focus on one simple business rule, there is a good chance you will illustrate a solid foundation. You can then compare this to the existing DB and implement change where needed.

If you create a query in a blank data base that looks at a specific event, you can compare this structure to the existing DB. Then take a look at Dal's post again. You need to somehow store the multiple entries of the "area" in another table. Something hard to imagine when you look at someone else's jumbled up creation, ie the existing DB.

[Dal Jeanis]

Some notes on your design -

I) Generally, it is best to have the key for a table have a more specific name than "Code"
Consider DeptCode or DeptCd or DeptID and AreaCode or AreaCd or AreaID instead.

II) tblFixedAssetsFMV.AREA links to tblLegendArea.Code, but tblLegendDept.AREA links to tblLegendArea.Area

Only one of those two fields on tblLegendArea (Code, Area) should be used as the foreign key on another table. Pick one of the two and use the same one wherever another table needs to refer to an Area record.

III) The cycle between tblLegendArea, tblLegendDept, and tblFixedAssetFMV indicates a design issue.

What the particular issue really IS depends on how the database is going to be used, and what the business procedures are, and what entities the tables are supposed to represent. There's a bunch of different valid ways that the database could be set up, depending on business rules and entity identification.

Example One - If a department can have many areas and an area can be in many departments.

tblLegendDept  
   DeptID      (PK, Autonumber)
   DeptName    Text 
   DeptNotes   Memo

tblLegendArea
   AreaID      (PK, Autonumber)
   AreaName    Text
   AreaNotes   Memo 

tblValidAreas
   DeptID       FK to tblLegendDept
   AreaID       FK to tbllegendArea

tblFixedAssetFMV
   DeptID       FK to tblLegendDept
   AreaID       FK to tbllegendArea
(with the constraint that the combination must be in tblValidAreas)

Example Two - If two departments can have have areas that are named the same, but they aren't REALLY the same. Note that DeptID is not present on the FixedAsset table, because the tblLegendArea records each connect to a single DeptID.

tblLegendDept
   DeptID      (PK, Autonumber)
   DeptName    Text 
   DeptNotes   Memo

tblLegendArea
   AreaID      (PK, Autonumber)
   DeptID      FK to tblLegendDept
   AreaName    Text
   AreaNotes   Memo 

tblFixedAssetFMV
   AreaID      FK to tblLegendArea

Example Three - If a fixed asset belongs to a department but might be kept anywhere, and the areas are independent. Note that there is no relationship between Dept and Area in this design.

tblLegendDept  
   DeptID      (PK, Autonumber)
   DeptName    Text 
   DeptNotes   Memo

tblLegendArea
   AreaID      (PK, Autonumber)
   AreaName    Text
   AreaNotes   Memo 

tblFixedAssetFMV
   DeptID       FK to tblLegendDept  (owning department)
   AreaID       FK to tbllegendArea  (current area)

[ItsMe]

That's some serious homework you did there Dal!

[hrenee]

Ohh ok, now you have my brain working. I believe for what we are trying to do example 1 is what I want to do.

And as I was looking at the design it dawned on me there was an issue with the areaID going to both tables.

I'm trying to think this through and I really appreciate the help.

I need:

tblArea - lists the 9 or so areas around the production area
tblDepartment - lists all the departments out, regardless of what are it is in
tblCombinations- lists all the possible combinations that could happen, of area and dept


This makes sense to me, hopefully I'm understanding you correctly.

When I change my tblFMV to the FK of the original (not combination table) I understand how to say, the value of that specific field must be in this related tbl, but I don't know how to say, this field and that field together must equal something in the tblCombo. or would it be easier to just put whatever the ComboID is, and then I can run queries off that later to tell the person receiving the report exactly which combo that is.

I really hope that makes some sort of sense.


And yes thank you both so much! My boss is the one who put this diddy together, and so going to him and saying, "uhhh this is all sorts of not right but I don't know how to fix it", is not really a viable option. And we're all accountants by trade, so really out of my depth.

[Dal Jeanis]

You're doing fine.

Now, tblCombinations is only needed if you need to verify whether the combination on a particular FMV record is valid. If that's not necessary, then you don't need that table.

Also, even if you do want to validate the combination, you don't have to use all the bells and whistles of the database that will automatically enforce the multifield-foreign-key constraint. (That's database talk for "valid combinations".)

Instead, you could build the verification logic into the data entry form.

Or, you could just run a report occasionally to see if anyone has miscoded an FMV record into a department/area combination that isn't valid, and manually fix them.

That's a design choice. Do whatever makes sense to you. Use a method that YOU understand, because you have to support it.
And you can always change your mind, or make improvements, later.

[hrenee]

Oh my this is so much easier than I had hoped, though it took me the better part of the day to get here. . . You're absolutely right. There's no reason to even have a 3rd table. If I say under Area it's PEA and under department it's WARE. Well gosh darn it, that's what it is, there aren't any constraints!

I feel a bit of an idiot now.

[Dal Jeanis]

The source of your confusion was the unneeded/redundant connections that someone built into the database.

The reason that I gave you three options to fix that structural error is that each of the three options would be valid for some business, somewhere. You now have determined that option three is most appropriate for your business.

Now that the fog is starting to lift, go over to Access MVP Roger Carlson's blog at http://www.rogersaccesslibrary.com/ and review his brief tutorials on database design. Do at least one of the sample database exercises.

It will be well worth your time to do that now, because then you can look at your remaining overall design and see if there are any more little structural "gotchas" built into it. Handling them NOW, before you add more infrastructure on top of them, is your best use of work effort. Hours spent verifying your design NOW will pay back roughly fifty to one in time saved later.

[hrenee]

Again thank you. I've been working on his stuff all morning. and also reading the stuff at accessmvp.com. I think what gets me into trouble is I've taken a few classes, but never had to put those classes into an actual DB. I'm finding that data isn't nearly as easy to work with when you are starting from someone else's interpretation.

And somehow your option #3 didn't really sink in as the right one even when I read it. I was just sure they had to have a relationship, because someone else said they did.


ETA: This can be marked as solve (and if there's a dunce cap feel free to put that on there as well)