proper database design vs front end usability

[Richie27]

Okay - one last stab! Does this look fully normalized? I think so. Phew. latest.doc

[ssanfu]

Okay - one last stab! Does this look fully normalized? I think so. Phew.

Til the next time...


------------------
Looking at the tables "tblStaff", "tblAuthorisation" and "tblFileActionPerson". Couldn't you just use "tblStaff" by adding two boolean fields "Authorized" and "Actionperson"? If a staff person is Authorized, set the field to TRUE. Same with "FileActionPerson", if a staff person is an "Action" person, set the field to TRUE.
For an "Authorization" combo box (not an EVIL look up field), the row source SQL would be

SELECT staff_id, staff_number & " - " & lastname & ", " & firstname FROM tblStaff WHERE Authorized = TRUE

Bound field = 1
Column count = 2
Column width = 0"


For an "Actionperson" combo box (not an EVIL look up field), the row source SQL would be

SELECT staff_id, staff_number & " - " & lastname & ", "  & firstname FROM tblStaff WHERE Actionperson = TRUE

Bound field = 1
Column count = 2
Column width = 0"

Two less tables and two less forms....


------------------
Why can't these two tables "tblSuResidentialServices" & "tblDayService" be combined? And "tblSuResidential" & "tblSuDayService". What are they? Sample data?

You know how it is with inquiring minds......

[Richie27]

I love you Steve lol! In a purely platonic way of course!! You made some great points that make a lot of sense - I will incorporate pronto! I'm feeling better and better about this project! Pity I'll be on it over the weekend but sure that's deadlines for ya!! Cheers!

[Richie27]

Ti


For an "Actionperson" combo box (not an EVIL look up field), the row source SQL would be

SELECT staff_id, staff_number & " - " & lastname & ", "  & firstname FROM tblStaff WHERE Actionperson = TRUE

Bound field = 1
Column count = 2
Column width = 0"

Two less tables and two less forms....


------------------
Why can't these two tables "tblSuResidentialServices" & "tblDayService" be combined? And "tblSuResidential" & "tblSuDayService". What are they? Sample data?

You know how it is with inquiring minds......

They aren't sample data - they are services that will have been used by the people about whom files will be stored!

[Richie27]

Why are lookup fields "evil"? Is there really much difference between one and the other?

[ssanfu]

Why are lookup fields "evil"? Is there really much difference between one and the other?

Yes. See
http://access.mvps.org/access/tencommandments.htm

and

http://access.mvps.org/access/lookupfields.htm


---------------------------------------------
Other discussions:

The Evils of Lookup Fields in Tables

Contributors

Arvin Meyer
Joan Wild
A Lookup field in a table displays the looked-up value. For instance,
if a user opens a table datasheet and sees a column of company names,
what is in the table is, in fact, a numeric CompanyID, and the table
is linked with a select statement to the company table by that ID.

Any query that uses that lookup field to sort by that company name
won't work. Nor will a query that uses a company name in that field as
a criteria. If a user creates a combobox to select the company using a
value list, the data in the table can be over-written.

Another relationship is created which then creates another set of
indexes when a Lookup field is created, thus bloating the database
unnecessarily.

If a combobox based on the lookup is used in a form, and a filter is
applied, the persistent filter effect of Access often saves the filter
and the next time the form is opened, there will be a prompt for the
value (which cannot be provided, thus creating an error).

Reports based on the lookup field need a combobox to display the data,
causing them to run more slowly. The underlying recordsource can also
be modified to include the table, however the index, (unless it was
set up within a proper relationship) may not be optimized.

Lookup fields mask what is really happening, and hide good relational
methodology from the user.

The database cannot be properly upsized to, or queried by, another
engine (without removing all the lookup fields) because no other
engines use or understand them.

If security is implemented, permissions to tables is usually denied,
and RWOP queries are used for data access. There will often be errors
that there are no permissions on a specific table that isn't even
being used in a query (because the lookup field is). If the queries
are nested or complex, it can take some time to track down the lookup
that's causing the error (that is, if it occurs to you).

* -------------------------------



Lookup Fields are, indeed, wicked, evil, the very dark spirits of
computering incarnate... a punishment visited on the Great Access Unwashed
by a cabal of conspirators in Redmond WA because we were insufficiently
opposed to *pen S*urce.

They can be useful to the novice end-user who views data in Table Datasheet
view. They have been known to reduce the same novice end-user who tries to
use that Field in a Query to a shambling, glaze-eyed, thick-tongued Zombie
state -- they know they have been seeing "Doctor", "Lawyer", "Beggarman",
"Thief" in the Datasheet, but the Query returns "1", "2", "3", and "4".
Some rock-star Product Manager or Designer thought it would be helpful to
users to "automate" the concept of a Joined "Lookup Table", but it has
clearly been more trouble to users and to people answering questions in the
newsgroups than it could ever have been help to end-users in datasheet view.

A "Lookup Table" will have a Primary Key and other information (like a name
or description Field), referenced by a "Foreign Key" in another table.
Properly JOINed on the Primary and Foreign Key, the Query will show the
other information, but looking at the Table will show the Primary Key.

With the "Lookup Field" tab, this has been automated (without telling you,
of course), so that even in Datasheet View, you see the "other information",
but what's actually stored in your table is the Foreign Key. A Query does
not delve deep enough into the field properties to automatically compensate
for the design flaw, so shows you the Foreign Key, which prior to that time,
you had no idea existed.

Some of the discussion here hasn't been as clear as it might in making the
distinction between "Lookup Fields" and "Lookup Tables". But you have asked
the proper question in your subject line:

Ans. "Lookup Fields" are, in truth, "evil" because they obscure what is
actually in the Table; "Lookup Tables" are a proper approach because having
implemented them yourself, you know what is stored in each of the two Tables
involved and won't be "unpleasantly surprised".

Larry Linson
Microsoft Office Access MVP

[ssanfu]

They aren't sample data - they are services that will have been used by the people about whom files will be stored!

I was asking what the data would look like, ie Sample data. Same type of data that is not of a sensitive nature.

[Richie27]

Yes. See

Ans. "Lookup Fields" are, in truth, "evil" because they obscure what is
actually in the Table; "Lookup Tables" are a proper approach because having
implemented them yourself, you know what is stored in each of the two Tables
involved and won't be "unpleasantly surprised".

Larry Linson
Microsoft Office Access MVP

Ah, I see.

I have learned quite a bit from you sensei! Mucho Gratias!

[Richie27]

Yes, Yes. Nowt sensitive about the data. I have implemented it into one table now. That just means getting rid of a few lookup columns and changing a bit of code and I'm done.

Whoop, whoop!

---------------------
"Dry land is not a myth, I've seen it!"

[ssanfu]

Glad to help, weed-jumper!!

[Richie27]

Just had a thought, not sure if it's a problem or not but maybe your expertise could advise.

Was just looking at my table structure again.

You know how I now have a file table (1) and an events table (many)? Was just thinking that an instance of event can be applied to many file instances all at once so therefore, perhaps my tables are not as normalised as I thought. Should I have an intermediary table between file and events or would it be okay as is? Are there any pitfalls as is and what would they be?

[ssanfu]

You know how I now have a file table (1) and an events table (many)? Was just thinking that an instance of event can be applied to many file instances all at once so therefore, perhaps my tables are not as normalised as I thought. Should I have an intermediary table between file and events or would it be okay as is?

You are referring to "tblSUFile" and tblFileEvents"? One file can have many events and one event can happen to many files?
You are most of the way there now. Even the junction table is named correctly ('tblFileEvents"). You just don't have the other 1-to-many table.

Using a lookup table (not a lookup field!)will ensure consistancy in data entry. If you are entering text in the "actionDescription" field, there is a chance of misspelling or use of different terms.
If you create a new table, "tblEvents", and change the field type for the field "fk_actionDescription" to "Number - Long" in "tblFileEvents", you can use two FK fields : "fk_FileId" and "fk_actionDescription" as the PK in "tblFileEvents". It would look like this:

Attachment 7612

You could have one field ("actionDescription" of type Text) in the "tblEvents", but I like to have an autonumber in every table.

Are there any pitfalls as is and what would they be?

Not really. Maybe more work to ensure data consistency/integrity.



Note:
I tried to use your naming convention, ie "fk" prefix for a field name, but I added an underscore for clarity.
"fk_actionAuthorisier" is a number (long) type field for a FK to link table "tblStaff" ("staff_id") to the table "tblFileEvents".


Clear as mud????

[Richie27]

that is crystal clear matey! ;-) that is super.

I will have to start using my own initiative again! I am getting far too dependant on the knowledge of those like yourself!

It is good to have reassurance though, when you're not certain or confident in what you're doing and you really have no one else to ask.

Thanks for all the pointers!

Someone else uses the fkEvent format and posted reasons why it is better than the _. Can't remember for the life of me what they were but it seemed to make a lot of sense at the time. Saves a key stroke too! ;-) all those seconds add up when deadlines are looming ;-)