Creating My First DataBase Critique

[Kyp]

Gents,
After ready lots and lots of info provided by you guys, I've laid out my data tables in an excel spreadsheet, a different sheet for each table.
I hope you guys don't mind taking a look and let me know if I am on the right track here.
I haven't put anything in access yet, just preliminary stuff, sorry...

Thanks!
73!

 

[plog]

Set your autonumber to increment, its sole purpose is to be unique. Never knew, much less used, that there was a Random option.

1. You've over normalized with lookup tables. Tables with just 1 real field of data (autonumbers aren't real data, they are internal to the database) do not need to exist. So, instead of having the numeric field [EventPriorityID] in tblEvent is unnecessary. Instead it should just be a text field where you store the actual value (e.g. Highest, Highe, Normal, etc.). You've done this a lot.

Now, you can also solve this by combining all your lookup tables into one master lookup table. You do this by adding a new field to designate what type of lookup the record is for.

2. You have both AssetID and AssetNumber as foreign keys in tables (tblMainenance, tblEvent) --you only need to link to the primary key of tblAsset which is AssetID.

3. Possibly--Circluar relationships. tblAsset has FacilityID, tblEvent has AssetID and FacilityID. That means the facility for the Asset could be different from the Event--is that correct? If not, then FacilityID probably comes out of tblEvent.

4. Possibly--Calculated values. You don't store values you can determine from other data. Often Status is calculable, I'm not sure about your data, so answer this: For an event, you know when its been opened, you know if its been assigned, you know if its been closed--with all that information can you logically deduce its status without actually knowing which status has been assigned to [EventStatusID]? If so, you don't need to store the Status, you just deduce it.

 

[June7]

Mostly agree with plog's comments. I disagree somewhat with item 1.

In addition, advise not to use space nor punctuation/special characters in naming convention. You have avoided spaces but using punctuation such as hyphens and slashes. They will work but will have to remember to enclose in [ ] when referencing those fields or controls.

 

[Kyp]

Set your autonumber to increment, its sole purpose is to be unique. Never knew, much less used, that there was a Random option.

I’ve read somewhere, when multiple users would be in the db at the same time, setting the autonumber to random would lessen the possibility of a conflict. (This will eventually be a split db. FE/BE)

1. You've over normalized with lookup tables. Tables with just 1 real field of data (autonumbers aren't real data, they are internal to the database) do not need to exist. So, instead of having the numeric field [EventPriorityID] in tblEvent is unnecessary. Instead it should just be a text field where you store the actual value (e.g. Highest, Highe, Normal, etc.). You've done this a lot.

Gotcha. I completely understand. Maybe I’m overthinking the relationships.

Now, you can also solve this by combining all your lookup tables into one master lookup table. You do this by adding a new field to designate what type of lookup the record is for.

I should create a master lookup table with all the data and just direct a combo box on a form to the correct column number and so forth, correct?

2. You have both AssetID and AssetNumber as foreign keys in tables (tblMainenance, tblEvent) --you only need to link to the primary key of tblAsset which is AssetID.

The AssetID would be the autonumber assigned to the asset while the AssetNumber is a description given to a device at the facility. (PT-2100 pressure transmitter 2100)

3. Possibly--Circluar relationships. tblAsset has FacilityID, tblEvent has AssetID and FacilityID. That means the facility for the Asset could be different from the Event--is that correct? If not, then FacilityID probably comes out of tblEvent.

With assets assigned to facilities. I was looking for a way to track facilities, their assets, and events. Events always pertain to an asset at a specific facility.

4. Possibly--Calculated values. You don't store values you can determine from other data. Often Status is calculable, I'm not sure about your data, so answer this: For an event, you know when its been opened, you know if its been assigned, you know if its been closed--with all that information can you logically deduce its status without actually knowing which status has been assigned to [EventStatusID]? If so, you don't need to store the Status, you just deduce it.

with the event being assigned to an employee, the employee must manually enter the status as events can linger for a long time, especially if waiting on parts.

I don’t know if this is going to post my reply correctly, I’m trying to reply from my phone. Sorry if it looks all messed up.

Thanks for the answers and hope that I am making sense in my reply.
-Kyp

 

[Kyp]

Mostly agree with plog's comments. I disagree somewhat with item 1.

In addition, advise not to use space nor punctuation/special characters in naming convention. You have avoided spaces but using punctuation such as hyphens and slashes. They will work but will have to remember to enclose in [ ] when referencing those fields or controls.

Gotcha. I will remove any special characters to avoid future issues.
Thanks!

 

[plog]

MajP gave a good example of the master lookup table in your prior thread:

Solved - New guy with a table structure question...

Hello gang😊 I have started an access project and after searching the forum, I have a couple questions on table structure. Questions: I've read in several threads that I should NOT use any type of "Lookup" in a table structure. No "Combo Boxes", or "Value List", period. I understand but why...

www.access-programmers.co.uk

---

Only AssetID should be stored in a foreign table. That's the whole reason we use primary/foreign keys.

---

Sounds like Facility needs to come out of Events then.

---

Your response about status did nothing to negate nor support my advise.

 

[Kyp]

Only AssetID should be stored in a foreign table. That's the whole reason we use primary/foreign keys.

Understood.

Sounds like Facility needs to come out of Events then.

An event will always occur at a facility. When creating an event, the facility will need to be identified. Should I still remove facility from the event table?

Your response about status did nothing to negate nor support my advise.

Not trying to negate you’re advice, just trying to learn and understand things as I venture into this unknown world!

I will attempt to create the tables and relationships from the advice from everyone and post the db to the forum in the morning. I’m trying to learn as I go.
Thanks guys!

 

[plog]

An event will always occur at a facility.

But it doesn't happen at a facility independent of an Asset. Since an Asset is tied to a facility and an event happens to an Asset; you know where that event occured and do not need to store the facility with the event.

My last response about status was that you didn't respond to my point/question. You just explained the process further without shedding light on the issue I raised. Can you logically deduce the status just by looking at the other data you store? Yes=status field not required, No=status field required.

 

[Kyp]

But it doesn't happen at a facility independent of an Asset. Since an Asset is tied to a facility and an event happens to an Asset; you know where that event occured and do not need to store the facility with the event.

You are correct, but not 100% sure I understand.
What I think I'm understanding is that the facility "field" doesn't need to be in the tblEvent but, the facility would be part of the asset record therefore, when the AssetID is polled, the facility information would follow?

4. Possibly--Calculated values. You don't store values you can determine from other data. Often Status is calculable, I'm not sure about your data, so answer this: For an event, you know when its been opened, you know if its been assigned, you know if its been closed--with all that information can you logically deduce its status without actually knowing which status has been assigned to [EventStatusID]? If so, you don't need to store the Status, you just deduce it.

I see what you are saying but not sure I understand.
What I think you are saying is that I do not need the [EventStatusID] in a table as a lookup. It's simply open or closed.
When an new event gets created, the event would essentially be open. The only interaction by the user would be to close the event after it is remediated.

Please forgive my ignorance....

 

[plog]

You got it exactly right for the first one.

For the second one, let's use an example that's close but not exactly yours. You have a table and in that table you have a [InitialInspectionDate], a [PartsOrderedDate], a [PartsReceivedDate], a [RepairDate] and a [CompleteDate]. You also have a Status field with 4 possible values (Being Inspected, Waiting On Parts, Being Repaired, Completed). You actually don't need the Status field because you can look at all those dates and know where it is in the process by what dates you have/don't have.

I don't know your data well enough to say if that is true, so I was asking if it was.

 

[Kyp]

You got it exactly right for the first one.

For the second one, let's use an example that's close but not exactly yours. You have a table and in that table you have a [InitialInspectionDate], a [PartsOrderedDate], a [PartsReceivedDate], a [RepairDate] and a [CompleteDate]. You also have a Status field with 4 possible values (Being Inspected, Waiting On Parts, Being Repaired, Completed). You actually don't need the Status field because you can look at all those dates and know where it is in the process by what dates you have/don't have.

I don't know your data well enough to say if that is true, so I was asking if it was.

Aha, I see what you are saying now.

Being that a report will be generated daily pertaining to open and closed events. Somehow, other than someone looking at dates or other info, the report query will have to know the event status. Also, the plan is to enable record locks. When an event is closed, (if I can figure it out), the event record will be locked and only a manager would be able to reopen it for any reason. At this point, I think I will have to leave the status as is but I made it a text field. Combo box from form would grab the data in the master lookup table.
I hope that make sense...

Kyp
73!

 

[Kyp]

I've taken all the info that I have understood and created the tables.
I also created the relationships, don't know if they are correct but I tried.
Tried creating a form and putting in some dummy data but getting errors. "You cannot add or change a record because a related record is required".
Also the combo boxes have the right data but I have to scroll all the way down to the bottom of the list....

Not too much interested in the error right now, mainly concerned with my table structure and relationships. Apparently I have something wrong!

Thanks!
-Kyp

 

[June7]

AssetID and FacilityID fields in tblEvent are set with a Default Value of 0 (unfortunately this is done by default by Access). There is no record in those lookup tables with ID 0. Remove that from the field settings for all foreign key fields. Why are these not comboboxes?

If you want to use a master lookup table like that, will have to use WHERE criteria to exclude rows with null fields. Null always sorts before other data when ascending.

SELECT EventPriority FROM tblMasterLookup WHERE NOT EventPriority IS NULL ORDER BY EventPriority;

I don't think this was the advised structure for a master lookup table. Instead of 9 fields there should be only 2 or 3.

ID	Element	Attribute
1	Ball	AssetCategory
2	Butterfly	AssetCategory
3	Accumulator	AssetGroup
4	Analyzer	AssetGroup
5	On Line	AssetStatus
6	Off Line	AssetStatus

Then combobox query like:
SELECT Element FROM tblMasterLookup WHERE Attribute = "EventPriority" ORDER BY Element;

 

[Kyp]

AssetID and FacilityID fields in tblEvent are set with a Default Value of 0 (unfortunately this is done by default by Access). There is no record in those lookup tables with ID 0. Remove that from the field settings for all foreign key fields. Why are these not comboboxes?

I did not notice that until you called it to my attention.
I have removed the "0" as well as set the fields to requires = Yes and allow zero length = No ( no one said to do this, I was just thinking this should be the case)

If you want to use a master lookup table like that, will have to use WHERE criteria to exclude rows with null fields. Null always sorts before other data when ascending.
I don't think this was the advised structure for a master lookup table. Instead of 9 fields there should be only 2 or 3.

I think I have misunderstood an earlier comment stating that I could have a master lookup table.
For tblMasterLookup, should I break the table down into 3 separate tables? tblAssetLookup, tblEventLookup, tblMaintLookup?

My end goal with this is to properly set this up to where can build upon it in the future if needed.

Thanks!

 

[Kyp]

I've changed a few things up and added a couple records in each table.
I am not sure how to split up tblMasterLookup to make it correct. Splitting the table into multiple tables was what I originally had but seems like too many tables.
I may just be overthinking it?

I haven't figured out (yet but researching) how to populate the facility text box on frmEvent from the asset number in the combo box.

Here is the db, please critique.

Cheers!

 

[June7]

I did not suggest to break up the master lookup table. What you have can be made to work, it's just not optimal design. Did you not understand the revision I suggested?

Don't need to save FacilityID in tblEvent. Facility info can be retrieved when needed in a query linking tblAsset with tblFacility. But first, AssetFacility field needs to be a number type because you are storing FacilityID as foreign key. Then combobox RowSource:

SELECT AssetID, AssetNumber, FacilityName
FROM tblFacility INNER JOIN tblAsset ON tblFacility.FacilityID = tblAsset.AssetFacility
WHERE (((AssetNumber) Is Not Null))
ORDER BY AssetNumber;

And expression in textbox ControlSource: =AssetNumber.Column(2)

Also, Access has a 2GB size limit. Saving lots of objects in attachment field uses up this limit. If db will stay small this may not be an issue.

 

[Pat Hartman]

You've over normalized with lookup tables. Tables with just 1 real field of data (outnumbers aren't real data,

@plog - you make this recommendation quite frequently and I always respond with posting my table maintenance application which I'm going to do here also. What I don't understand is why? Why would you want the developer to individually manage the lookups? Why would you want to maintain the lookup lists in multiple places? Why would you not want to have the ability to deactivate a value? I don't use separate tables for each list because it adds a lot of work that I can avoid by using my table maintenance method.

 

[plog]

I don't think my method does require maintenance in multiple places. In a database where the main table has no data you can just put the acceptable values into the drop down used to populate it. In a database where the main table does have data, you can use a query into that field for distinct values to get acceptable values.

If a field is deactivateable that's a piece of real data and now the table has 2 pieces of real data and you should use a table. None of the initial posters tables meet that.

 

[Pat Hartman]

@Kyp,
I forgot to mention that your relationship between Event and Asset appears to be backwards unless I don't know what an asset is. If you can have more than one asset assigned to an Event, then you need a junction table between Event and Asset so you can assign multiple Assets to multiple events. You would remove AssitID from the Event table since that restricts you to a single asset for an event. Regardless, it is incorrect to have AssetNumber in Event. It should exist ONLY in the Asset table.

I would also not prefix all column names with table information. This just forces you to type more characters before useful intellisense kicks in and it hides the actual column name when you open the table in DS view or a query because all you see are the prefixes and maybe one or two characters of the actual column name.

For column names that occur in multiple tables such as the address fields, I wouldn't worry about the "duplication". If you have to use the two tables in the same query, you can alias the names from one table using a prefix to disambiguate the reference.

And finally, although there is nothing wrong with the Attachment data type per se, you need to be aware that it is not supported by SQL Server or other RDBMS' so if you ever have to upsize, you would need to not only convert the data manually, but also modify the app since you would need to handle OLE objects differently from Attachments. If you don't envision ever having to upgrade the BE, then the Attachment data type is fine although for images, I would still recommend storing the path rather than the object itself because Jet/ACE bloat easily when you embed objects. SQL Server is better equipped to handle OLE objects.

 

[Kyp]

I did not suggest to break up the master lookup table. What you have can be made to work, it's just not optimal design. Did you not understand the revision I suggested?

I understood that the table was not optimal but I don't know what or how to optimize the master lookup table. I am striving to create this correctly so if you don't mind, please explain. I did not understand the comment on the revision.

Don't need to save FacilityID in tblEvent. Facility info can be retrieved when needed in a query linking tblAsset with tblFacility. But first, AssetFacility field needs to be a number type because you are storing FacilityID as foreign key. Then combobox RowSource:
SELECT AssetID, AssetNumber, FacilityName
FROM tblFacility INNER JOIN tblAsset ON tblFacility.FacilityID = tblAsset.AssetFacility
WHERE (((AssetNumber) Is Not Null))
ORDER BY AssetNumber;
And expression in textbox ControlSource: =AssetNumber.Column(2)

Understood, I will make that change.

Also, Access has a 2GB size limit. Saving lots of objects in attachment field uses up this limit. If db will stay small this may not be an issue.

I have read that while searching on the forum and somewhere it said that I could have a separate folder, outside the tables that could store attachments. It would be beneficial to retain the attachment field for attaching a data tag from a piece of equipment to the asset. When an asset would need to be replaced, the information would be readily available however, if I cannot store image files in a separate folder, I may have to just skip it or figure out another way. I surely do not want to exceed the 2GB limit of the db.

Thanks!