Is this a Sound Database Design?

Hi Curtis,

Normalizing Data

* storing data efficiently


|--- ...When you are planning your database... ---|

Instead of thinking about what you want out ...

....think about what you have to put in and structure it well.

Drawing an analogy to a building:

Data structure is the foundation.

Forms are walls.

Reports are paint.

Organize your data so the structures are strong. You want what you
build on that foundaton to be stable and flexible.

The best solution is simple... but it is the hardest to find.

It takes thought and foresight to design your structures well. And the
simplest solution is rarely easy to see. Get the structure right before
building anything else. If you have already built forms, queries, or
other objects, fix the structure before building any more.

Just as you wouldn't build a house on sand without a foundation, your
data structure is the foundation you will build on for Access.
It takes time to plan your database.

Here are some ideas for you:

Think of the different "nouns" that you will track:

Customers
Addresses
Cities
Phones
Notes
Products
Purchases
Payments
Campaigns

For each "noun", you describe it with "adjectives", or fields. Each
table should have a primary key. It is common to use the autonumber
field and "ID", such as CustomerID, , ProductID.

By convention, when "ID" is used in a fieldname, it indicates that field
was originally created by an autonumber and it is a LONG INTEGER data type.

Do not use "ID" as a fieldname as it is ambiguous and not descriptive.

Don't repeat data structures

If you see that you have address information in more than one table,
create an Addresses table and use an autonumber AddressID to relate the
data.

Key Fields

Key fields are used to link related tables together and long integers
are commonly used. When you create an autonumber field in access in one
table, create a long integer field in another table to match up to it.
The field names in the different tables should be the same when they
represent the same piece of information.

DefaultValue

Change default value of all numeric foreign key fields to Null --
Microsoft default is 0, which will never match with an autonumber field
-- not changing it will prevent you from being able to enforce
referential integrity if it is not specified. To hammer that in...
Access sets the DefaultValue of numeric fields to 0 -- this is not good
for a foreign key because there won't be a key field to match it to : It
is okay if it is not filled out, but it is NOT okay if it is filled out
with a value that doesn't match.

Indexes

You can also create indexes on tables. For instance, you may have a
combination of fields that should be unique, such as TestID and
QuestionID, where you would not want the same question to appear on a
test more than once. You can create a unique index on the combination.

Think of which fields you will be doing lookups on and build indexes,
but do this judiciously as indexes are updated when records are changed,
so they take extra time to maintain, and there is a limit to how many
you can create.

Generally, you will have several Lookup tables. For instance, Products
would be a lookup table when you want to collect a ProductID in another
table, such as Purchases.

Do not store names, store the IDs. For instance, do not store "Sue
Smith", but use the PID (PersonID) that identifies her (* see Combobox
Example on how to do this).

Lookup Fields in the table design

Another thing Microsoft allows you do is define comboboxes as part of
the table design. I do not recommend this. Data should be entered via
forms, which is where you would set up comboboxes.

Data Type for Key Fields

Don't use ANYTHING but text or integer, or long integer for key fields
-- double-precision numbers are not accurate for keys. If you do use a
text field for a key, keep in mind that it needs to be short to be
efficient. It takes about 1 byte to store each character whereas long
integers only require 4 bytes to store. Normally, long integers are used
for key fields.

Names

DON'T use anything but letters, numbers, and underscores in fieldnames
and tablenames.

Don't use special characters in names (%, &, /, etc). Personally, I
don't even use spaces. Start all names with a letter not a number.
Using numbers usually indicates that the data is not normalized anyway.

If you start a fieldname with a number, you WILL have problems, so don't
ever do it. Microsoft allows you to do many things that kick you in the
butt later.

Think about how long text fields will be and set the Field size to
something other than the default of 50 characters. For instance, 30 is
usually long enough for cities, 10 long enough for zips, 14 for phone
numbers, 15 or 20 for last or first name (I let firstname be longer in
case there are 2 of them).

Keep names concise yet descriptive.

"Date" is a bad name for a field since that is a reserved word. Qualify
fieldnames, like CourseDate and SaleDate. One reason to keep fieldnames
short is consideration of the length of the field that the column
heading will be over since column headings can't wrap when you open a
table. Another is less to type when you are coding.

Name your fields and tables well. When you do create forms and reports,
name the controls to something logical before building any code. Names
like Text34 and Combo68 make it frustrating to code and even more so if
you need help and others have to decipher what those names really mean.
Personally, I like to make the Name property of the control the same
as the ControlSource property whenever possible.

When you have command buttons, name them cmdClose, cmdOpenReportMenu, etc.

***
some more considerations:

Although convention tells us to prefix tables with "tbl", I don't like
to do that. Takes a split second longer to scan for values. If you do
want to group your tables, use something short, like T_

Which is easier to read?

tblCustomers
t_ Customers
Customers

If you are going to use a long table prefix such as "tblABC" ...make it
quicker to read the important part -- the actual table name.

Which can you read faster ...

tblABCLocation
tblABCLevel

or

tblABC_Location
tblABC_Level

always keep Names concise yet descriptive

Numbers that aren’t numbers

Unless you plan to do math, set "numbers" to text data type. Especially
if they have symbols such as "(", "-" such as phone numbers. It is more
efficient, however, to store numbers as long integers than text because
the number of bytes of storage is less. Personally, I give that up for
keeping the symbols.

InputMask

When you use the InputMask property on a text field, choose to store
symbols in the field so when you do an export, they will be there. For
instance, without storing symbols, a phone number would be 1234567890
when exported as opposed to 123-456-7890 or (123) 456-7890.

Allow Zero Length

Make sure Allow Zero Length is set to Yes for text fields if you are
planning on importing or using APPEND queries to add data and fields may
be empty strings.

Captions

Don't use captions for your fieldnames in your table designs.
Personally, I think this is a bad idea. Users should never enter
information directly into a table. Therefore, the only people that
should be opening the table directly are administrators and using
captions hides the real field name.
If you want to call "ItemID" something else like TaskID, then NAME it that.

Descriptions

Fill out your field Descriptions! This is what the status bar text will
be set to when you slide that field onto a form. The StatusBar text
shows up in the lower left corner of the screen on the StatusBar when
you are in that field.


Tracking date record was created or updated

Add these 2 fields to all your tables (except lookups) and make them the
last 2 fields.

DateCreate, date, DefaultValue = Now()
DateUpdate, date – set on the form BeforeUpdate event

the best way to use the DateCreate field is to set a default value of
=Now()
in the table design.

For DateUpdate, make sure it is on your form (I put it in the form
footer and LOCK it. Then, use the Form BeforeUpdate event to set the value

me. DateUpdate = now()


Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table --

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name -- PID

ControlSource -- PID

RowSource --
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn -- 1

ColumnCount -- 3

columnWidths -- 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth -- 3
(should add up to the sum of the column widths)

if you have a listbox, make the width .01 MORE than the sum of the
columns to prevent the horizontal scrollbar.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields.

For instance

textbox:
Name -- BirthDate
ControlSource -- = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access

'~~~~~~~~~~~~~~~~~~~~~~~~

"You mean I would have to get the autonumber manually for each ..."

no, everything would be done in your code

'~~~~~~~~~~~~~~~~~~~~~

"But why have so many tables for, what are the reasons you say that for? "

You need to look down the road, Curtis. One of the reasons for using
Access to track your data is so that you can take advantage of its
incredible power -- as you learn more about Access, you will be glad if
you take the time now to structure your data well because you will have
more flexibility to do what you want.

Once you have historical data in your tables, you can see what your
database can tell YOU that perhaps you never thought of...


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
"...so I can't just go splitting everything up."

yes, you can ... just because your data comes to you in a flat format
does not mean that is how yu should store it...

you can have one table where you store the stuff to transfer to other tables

first append the info to the main table

then get the max autonumber key value

then append data to the other tables where it really belongs


You mean I would have to get the autonumber manually for each or easy as
creating an append query, hit a button a bamb? Also, when you say append the
info in the main table, you are talking about pasting the data right?

But why have so many tables for, what are the reasons you say that for?
Performance wise, etc? Is it not good to have a table with a whole bunch of
fields? Because that screenshot I show above, just shows a fraction of
them.... But a lot are just features, like account paid (yes/no), taking
Discover & Amex, etc....

Hi Curtis,

do not paste the second procedure into your code if you are only going
to use the first one -- both are named the same and you should decide
which one you want to use. You cannot have 2 prcedures with the same
name on the same module sheet. Since these are PRIVATE functions, it is
ok to have another Private function with this name in ANOTHER module sheet.

If you email me (and anyone else reading this post too), I will send the
first 3 chapters of a book I am writing on programming with VBA (email
address in my siggy) -- I can also send you a document called "Access
Basics for Programming", which is 30 pages -- make sure and tell me what
you want in the email message.

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
Hi Crystal,

It looks like you had three sets of code, but there are only two versions?
Sorry, but I'm still learing Access, always and I tried your very first set
of code. Created a form with a subform in it, named it TabSubform and then
created a command button, named it SwitchTabs. I then changed the names of
the forms in the code, like "Address_Sub" to the names of my forms, like
Potential, etc.

I tried that and it said: The expression On Click you entered as the event
property setting produced the following error: Member already exists in an
object module from which this object module derives.

You have to be sorta straight with me to know what you are talking about,
like create a form called, create command button called and use this coding
for on click, etc... :-)

Curtis

tab control vs replace SourceObject of subform

I have 2 versions -- the first hardcodes formnames to swap in the code.
The second uses a table (usys_tabs: the usys prefix makes it a
"system" object) to store the swapping information

The "command buttons" (put in quotes because I actually used label
controls) to switch subforms are colored so that you can see which one
is active

The NAME property for the labels to switch are (ie
Tab1
Tab2
Tab3
etc

'~~~~~~~~~~~~~~~~~~~~~~~~

Private Function SwitchTabs(pIndex As Integer)
On Error GoTo SwitchTabs_error
'9 is Notes -- no code, not launched

'crystal
'strive4peace2006 at yahoo.com

Me.TabNumber = pIndex
Dim mNumTabs As Integer, i As Integer, mboo As Boolean
Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long

Fore1 = 16777215
Back1 = 11220286
Fore2 = 8388608
Back2 = 16644084

mNumTabs = 14

For i = 1 To mNumTabs
If pIndex = i Then
Me("tab" & i).BackColor = Back1
Me("tab" & i).ForeColor = Fore1
Else
Me("tab" & i).BackColor = Back2
Me("tab" & i).ForeColor = Fore2
End If
Next i

Select Case pIndex
Case 1, 2, 3, 4, 5, 14
Me.TabSubform.Visible = True
Me.TabSubform.SetFocus
End Select
Select Case pIndex
Case 1
Me.TabSubform.SourceObject = "Address_Sub"
Case 2
Me.TabSubform.SourceObject = "Phone_Sub"
Case 3:
Me.TabSubform.SourceObject = "eAddresses_Sub"
Case 4:
Me.TabSubform.SourceObject = "Websites_sub"
Case 5:
Me.TabSubform.SourceObject = "Products_sub"
Case 14:
Me.TabSubform.SourceObject = "FindPeople_sub"
Case Else
Me.TabSubform.SourceObject = ""
Me.Name1.SetFocus
Me.TabSubform.Visible = False
End Select

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs"
'press F8 to step through lines of code to see where problem is
'comment next line after debugged
Stop : Resume

resume Proc_Exit

End Function

'~~~~~~~~~~~~~~~~~~~~~~~~

TabID is an unbound control -- it is set when the form loads

an example of the Onclick event for the Tab2 label is
=SwitchTabs(2)

I like this much better and it is prettier too!

Dummy is an unbound control -- really tiny because I use labels instead
of command buttons and a label does not get the focus

Sometimes, the subforms do not have linking fields, like a lookup subform

In those cases, I define a calculated control on the subform and set it
equal to the specified control on the main form for LinkMasterFields
(since the number of controls in LinkMasterFields and LinkChildFields
cannot be changed at runtime)

ie, in this example, I have set up an AddressBook application with PID
(people ID) as the main key as the first form to do switching. The
second form to do switching (below) is an Admin form to import and
export information.

unbound subform :

textbox control
name -- PID
ControlSource -- =forms!AddressBook!PID
visible -- no

and now I am taking this one step further:
table -- usys_Tabs
frmID TabID btnCaption frmName IsActive IsSib
1 0 Find FindPeople_sub Yes Yes
1 1 Address Address_Sub Yes Yes
1 2 Phone Phone_Sub Yes Yes
1 3 Email eAddresses_Sub Yes

2 1 Export Admin_Export Yes Yes
2 2 Import Admin_Import Yes Yes
2 3 Import Brio Admin_Import_Brio Yes Yes
2 4 Reports REPORTMENU_A Yes No
2 5 Utilities Admin_Utilities Yes Yes
'~~~~~~~~~~~~~~~~~~~~~~~~

Private Function SwitchTabs(pTabID As Integer)
On Error GoTo Proc_err

Dim mCurrentTab As Integer, i As Integer, mBoo As Boolean
Dim mform As String
Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long
Dim mLastTab As Long
Dim BackNotSub As Long
Dim r As dao.Recordset, S As String

'crystal
'strive4peace2006 at yahoo.com

'needs reference to:
'Microsoft DAO Library

mLastTab = 5

BackNotSub = 16112075
' If Me.Dirty Then Me.Dirty = False

mCurrentTab = Nz(Me.TabID)
Me.TabID = pTabID

'since I have this code behind the form it applies to
'the formID is hardcoded
S = "SELECT Tab.TabID, Tab.btnCaption, " _
& " Tab.frmName, Tab.IsActive, Tab.IsSub " _
& " FROM usys_Tabs AS Tab " _
& " WHERE frmID=2 ORDER BY TabID;"

'don't need to modify record, just look them up
'that is why we use dbOpenSnapshot

Set r = CurrentDb.OpenRecordset(S, dbOpenSnapshot)
r.MoveLast
r.MoveFirst

r.FindFirst "TabID = " & pTabID
Me.dummy.SetFocus

If (r.NoMatch) Or IIf(r.NoMatch, False, Not r!IsActive) Then
MsgBox Nz(r!btnCaption) & " is not active", , _
"Under construction"
Else
If r!IsSub Then
Me.TabSubform.SourceObject = r!FrmName
Fore1 = 16777215
Back1 = 11220286
Fore2 = 8388608
Back2 = 16644084

For i = 1 To mLastTab
If i 4 Then
If pTabID = i Then
Me("tab" & i).BackColor = Back1
Me("tab" & i).ForeColor = Fore1
Else
Me("tab" & i).BackColor = Back2
Me("tab" & i).ForeColor = Fore2
End If
End If
Next i

Me.TabSubform.SetFocus
Else
On Error Resume Next
DoCmd.OpenForm r!FrmName
End If
End If

Proc_exit:
On Error Resume Next
r.Close
Set r = Nothing

Exit Function

Proc_err:
MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs"
'press F8 to step through lines of code to see where problem is
'comment next line after code is debugged
Stop: Resume

Resume Proc_exit

End Function

'~~~~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
Hi Crystal,

Your awesome, it's great to have people out there willing to help! Access
is so darn cool as it can do endless things & still learning over the years
the power of this one program...

He http://www.gotmerchant.com/dbrelationships.jpg

I know, I need to get the spaces out of the names, but will fix that one
day....

Any way possible to view the VB for the entire database in one screen, so
you can change all the references to a particular name, use the replace tool
& get it all fixed very easily?

Got an example of what you mentioned below? You have one subform and
command buttons, which will change the subform, which form it displays? If
so, got an example I can see the code & see how that it is done & use it?

I only have one main table and a few others that include different info tied
to the main one, you see it in my pic above. The other tables are mere pull
down info tables, etc....

Its hard to make it look pretty & still have all the cool features & simply
all the stuff you want on one screen.....

Curtis

There are some good points there, thanks for all the info.

1. The only problem I have is you seem to create so many different tables,
seems too excessive, like you need one main table that everything relates to.
This should have all your customers or subject's main information like name,
addr, city, st, zip, phone, email, etc... Now I do need another one for
merchants as there can be more than one owner, like 3, etc. But in your
previous examples, you split it all up phone numbers, etc. Seemed to
excessive to me in that respect.

2. I'm not sure how you take one big table with every field in there, like
pasted all the info from the online app and then APPEND the data into the
multiple tables I have setup. I tried to setup a Append query and it only
allows you to append to one table, not a bunch of different ones. It asks
you what table do you want, you must pick in this db or some other database...

3. I do like the datecreate & dateupdate idea, would be a fart trying to
display those in the forms for every table, etc. Room wise, but it would be
there in case you need or want the data, etc. Very good. You could probably
go even further and have an author field as if you have multiple employees
and instead of overwriting updatefield, that would be a separate table it
self, so you can see whole all updated it historically, etc...

Thanks
Curtis

Sure, you can email it to me at sshost at Google's email address.

Also, I tried pasting just the first set of code & got the error I mentioned
above, anything you see wrong? I gave up on it after a while...


do not paste the second procedure into your code if you are only going
to use the first one -- both are named the same and you should decide
which one you want to use. You cannot have 2 prcedures with the same
name on the same module sheet. Since these are PRIVATE functions, it is
ok to have another Private function with this name in ANOTHER module sheet.

If you email me (and anyone else reading this post too), I will send the
first 3 chapters of a book I am writing on programming with VBA (email
address in my siggy) -- I can also send you a document called "Access
Basics for Programming", which is 30 pages -- make sure and tell me what
you want in the email message.

Crystal,

If I'm not mistaken, you can't have all these different forms, add them all
to the same query and use one main form, right? You will have to use
subforms for the additonal tables right? If so, then I can't go splitting it
up as I can't go spliting up the form that much, just wouldn't work.

Curtis

I meant to say you can't have all these different tables in the same query,
as I tried that. Then selected certain text boxes for those particular
fields from the other tables, which have the relationships in place, doesn't
work, guess all those fields must be in subforms, and that beats the whole
purpose?

Thanks
Curtis


If I'm not mistaken, you can't have all these different forms, add them all
to the same query and use one main form, right? You will have to use
subforms for the additonal tables right? If so, then I can't go splitting it
up as I can't go spliting up the form that much, just wouldn't work.

Curtis

Hi Curtis,

Rather than using just one form that gets its data from many tables (or
globbing all the data into one table when it should be seperated), most
database solutions use a mainform/subform scenario.

In my opinion, it is best to have just one form or subform to fill data
in each table. So, if you have 10 tables, you would have 10 data forms
-- then perhaps a "switchboard" form to decide which form to open and a
ReportMenu form for processing report requests.



Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
Crystal,

If I'm not mistaken, you can't have all these different forms, add them all
to the same query and use one main form, right? You will have to use
subforms for the additonal tables right? If so, then I can't go splitting it
up as I can't go spliting up the form that much, just wouldn't work.

Curtis

So is it even possible to have one form with text boxes in it pointing to
fields from multiple tables? It isn't right?

Rather than using just one form that gets its data from many tables (or
globbing all the data into one table when it should be seperated), most
database solutions use a mainform/subform scenario.

In my opinion, it is best to have just one form or subform to fill data
in each table. So, if you have 10 tables, you would have 10 data forms
-- then perhaps a "switchboard" form to decide which form to open and a
ReportMenu form for processing report requests.



Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
Crystal,

If I'm not mistaken, you can't have all these different forms, add them all
to the same query and use one main form, right? You will have to use
subforms for the additonal tables right? If so, then I can't go splitting it
up as I can't go spliting up the form that much, just wouldn't work.

Curtis

Crystal,

I'm still not able to get this code to work! Here is what I have exactly!

Created a form called TEST. Inserted a command button named SwitchTabs.
Inserted a subform named TabSubform.

I pasted this code as an OnClick for the command button. It comes up with
that same error. What do I do???? Potential & Current are the names of my
forms I want to be displayed in TabSubform.

==================CODE==============

Private Sub SwitchTabs_Click()
Private Function SwitchTabs(pIndex As Integer)
On Error GoTo SwitchTabs_error
'9 is Notes -- no code, not launched

'crystal
'strive4peace2006 at yahoo.com

Me.TabNumber = pIndex
Dim mNumTabs As Integer, i As Integer, mboo As Boolean
Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long

Fore1 = 16777215
Back1 = 11220286
Fore2 = 8388608
Back2 = 16644084

mNumTabs = 14

For i = 1 To mNumTabs
If pIndex = i Then
Me("tab" & i).BackColor = Back1
Me("tab" & i).ForeColor = Fore1
Else
Me("tab" & i).BackColor = Back2
Me("tab" & i).ForeColor = Fore2
End If
Next i

Select Case pIndex
Case 1, 2, 3, 4, 5, 14
Me.TabSubform.Visible = True
Me.TabSubform.SetFocus
End Select
Select Case pIndex
Case 1
Me.TabSubform.SourceObject = "Potential"
Case 2
Me.TabSubform.SourceObject = "Current"
Case 3:
Me.TabSubform.SourceObject = "Potential"
Case 4:
Me.TabSubform.SourceObject = "Current"
Case 5:
Me.TabSubform.SourceObject = "Potential"
Case 14:
Me.TabSubform.SourceObject = "Current"
Case Else
Me.TabSubform.SourceObject = ""
Me.Name1.SetFocus
Me.TabSubform.Visible = False
End Select

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs"
'press F8 to step through lines of code to see where problem is
'comment next line after debugged
Stop: Resume

Resume Proc_Exit

End Function
End Function
==================CODE==============

Thanks
Curtis

Curtis Stevens wrote:
So is it even possible to have one form with text boxes in it
pointing to fields from multiple tables? It isn't right?

It is if the RecordSource of the form is a query that joins multiple tables.
The potential problem is that multi-table queries are often not editable
unless built just right and on rare occassions can even send the updates to
the wrong table.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com