One Big Table or Union Queries

[EternalMyrtle]

I am at a junction right now that I need to make a decision about how my database will be set up for the long haul and I would appreciate any insight about the best method to proceed.

Currently, I have several tables in which I keep similar information (this is mostly a project management database--so I have several junction tables for ProjectsClients, ProjectsVendors, ProjectsConsultants, etc. plus similar tables for the related contacts ProjectsEmployees, ProjectsClientContacts, etc).

The information for each is not exactly the same. Having said that, with some minor adjustments, the information could be combined into one table that will end up having tens of thousands of records.

Or I can keep them as is and use Union Queries to intellectually combine the information that needs to be combined (mostly projects, companies and contacts in my forms, subforms and reports).

Any opinions on which is the better choice?

At this point, making good use of Union Queries would be the simplest choice . Is there a downside I should consider before relying on union queries to do this?

Thanks

 

[rzw0wr]

Can you show us a picture of your tables and relationships.

I try to stay away from single table databases.

Dale

 

[EternalMyrtle]

Ok, I got rid of several tables that are not pertinent. The ones that are pertinent to my question are concentrated on the left side of the image (see Relationships 2).

The tables in question are:

Projects_Clients (could be appended to tblProjectsCompanies)

Projects_Designers_Roles and Projects_ClientContacts could be appended to tblProjectsCompanyContacts

FYI: "Designers" from tblProjects_Designers_Roles is an FK field (the same as ContactID in the other tables). It is just poorly named.

Also, Designers are Employees so the CompanyID would have to be our company. It seems silly to have to add that extra data in as I would have to do if I combined this data into tblProjectsCompanyContacts. Plus, there would be benefits to maintaining a separate list for employees' project associations. Finally, another justification for keeping the existing table structure is that the roles vary depending on if a contact is a client, employee or a vendor/consultant.

Just a disclaimer that I started this with zero experience so it is far from perfect. Originally, it wasn't even intended to be a project management database. I created it because I needed a simple database to track boxes sent to storage and it just took off from there. Boxes were linked to projects and it kept growing...

Any suggestions at all would be helpful.

Thank you!!!

 

[rzw0wr]

1 Table will not work well.
I would take a long hard look at the tables and structure.
Make sure everything there is needed, see if any tables could be refined and make sure my relationships were as needed.

Dale

 

[way2bord]

Though keeping a database normalized is the general trend and is preferable for a good number of reasons (data integrity, organization, error reduction), denormalized tables can be referenced significantly faster (in certain cases).

What this means: Union queries are very very slow.

If you're running a single union query on your tables, then using data from that query - you're probably best off not worrying about table adjustments - just create your query and you're golden.

If, however, you see yourself running your union query -- and then needing to run additional queries off of the new union-query recordset - I'd start examining the tables to determine how you can efficiently denormalize them to speed up the process.

 

[rzw0wr]

Yes, what way2bord said.

Dale

 

[Pat Hartman]

Your column names are inconsistant and so looking at your schema is confusing. Some of your column names also include spaces which they shouldn't and finally, the tables all have a primary key named ID so you can't easily tell which FK it is supposed to link to. I have seen this a lot recently so there must be some guru out there espousing this idea. I disagree. Primary keys should be easily matched with their FKs without having to refer to the relationship diagram and not all have the same name.

Other areas of consistancy, when you track updated dates and updated by, always use the same name. So the columns in ALL tables should be UpdateDT and UpdateBy. There is no need to think up new names for each table.

I would use a "single" table rather than multiples unless there is a great difference in the columns. In that case, I would go with an entity and subclass schema so all the common fields are located in the entity table along with a type code and the distinct columns are located in several subclass tables. One for each type.

Union queries are not updateable so they are not suitable as the RecordSource of a form. To use the entity and subclass schema, make a main form for the entity and use the type code to determine which subform to display in the subform control.

 

[EternalMyrtle]

Pat, everything you say makes a lot of sense except that I don't know how to execute the "entity and subclass schema" suggestion.

In particular, I have found that having all the PKs called "ID" has become very confusing, especially for queries which I am using for record sets and for any code I am using. There was no guru advising this per say: I used the "Access Missing Manual" book and I don't think the writer ever advised against it (although I may have just missed that point) and this is what Access does by default.

As for the union query: yes, I would have to base a recordset off it for a couple of subforms so it sounds as if this will not work. I would like to do what you suggested but will need to learn how. Otherwise, I will again rethink the single table concept since most of the columns are the same. There would be certain benefits to keeping the tables separate but I need to think it through.

On a side note regarding dateupdated and updatedby fields... one of my tables (Projects) has a DateUpdated and UpdatedBy field for every field in the table. So, I now have triple the number of columns in the table. Is this ok or is there a better way to do this? All of the "update" fields are set automatically but it does bloat the table. Perhaps this should be posted in a separate thread...

I really appreciate all of your suggestions. I am the first to admit that I am in over my head with this whole project but concrete suggestions like these help me move forward and improve. Thanks

 

[Frothingslosh]

What about, perhaps, an 'Updated' table linked to your projects table via the PK. In the Updated table, you have the Project's ID, the name of the field that was updated, the date/time, and the user who did the change? For giggles, you can even store the old value.

Viola, changelog without tripling the table size!

And Access uses "ID" as a default name simply because you're able to create the fields in the table before you use the table. At the very least, I'd recommend your ID fields be named along the line of TableID, such as ProjectsID, CategoriesID, etc. Then in the tables where those are foreign keys (the linked tables), you use a field with the same exact name. It makes it easy to see how the tables relate that way, even without going to the relationships window.

I believe what Pat was talking about schema-wise was the table names. You have, for example:

• Projects
• Projects_Clients
• BuildingTypes
• tblProjectsCompanies
• TBLCompanies

That's five different ways of naming your tables, although 4 and 5 are admittedly treated the same by Access. What you normally want to do is pick ONE method and stick with it. I usually use a three letter prefix, myself, so I know if I'm referring to a table, a query, or whatever, but the main thing is to pick ONE method and stick with it.

 

[Pat Hartman]

As Froth said, consistancy is more important than the actual method you choose. I use style 4.
An example of an "entity" and its related tables.
tblEntity:
EntityID (autonumber PK)
CompanyOrLastName
FirstName
Addr1
Addr2
City
State
Zip
Country
EntityType (supplier, customer, employee)
...
tblSupplier:
EntityID (long integer, PK, FK to tblEntity)
specific supplier fields
..
tblCustomer:
EntityID (long integer, PK, FK to tblEntity)
specific customer fields
...
tblEmployee:
EntityID (long integer, PK, FK to tblEntity)
specific Employee fields

tblSupplier, tblCustomer, and tblEmployee are related 1-1 with tblEntity which is considered the "parent" table.

Your main form will be bound to a query of tblEntity. You will have three subforms, one for each of the other tables. In the Current event of the main form, you specify which subform to show.

Select Case Me.EntityType
    Case "Customer"
        Me.sfrmControl.SourceObject = "sfrmCustomer"
    Case "Supplier"
        Me.sfrmControl.SourceObject = "sfrmSupplier"
    Case Else
        Me.sfrmControl.SourceObject = "sfrmEmployee"
End Select

 

[EternalMyrtle]

Yes, I am in the process of renaming everything! Thanks for the example of an entity table, Pat. And thank you Froth for the idea of an update table.

 

[EternalMyrtle]

Froth, I do have one more question: what would be the best way of storing the old field value in my update table? I presume this should be done automatically but how? Is this what the "before update" event is for? Do I need to use vba for this?

Thanks again to both of you! Your responses are so helpful.

 

[Frothingslosh]

Yes, you would use the BeforeUpdate event. This link shows one way to do wit with minimal hassle:

http://www.techrepublic.com/article/a-simple-solution-for-tracking-changes-to-access-data/6166807

The code she wrote lost its formatting, so here it is cleaned up. It's based on using the table she described earlier, so keep that in mind.

Const cDQ As String = """"
Sub AuditTrail(frm As Form, recordid As Control)

[COLOR="SeaGreen"]'Track changes to data.
'recordid identifies the pk field's corresponding
'control in frm, in order to id record.[/COLOR]

Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String

On Error GoTo ErrHandler

    [COLOR="SeaGreen"]'Get changed values.[/COLOR]
    
    For Each ctl In frm.Controls
        With ctl
        [COLOR="SeaGreen"]'Avoid labels and other controls with Value property.[/COLOR]
            If .ControlType = acTextBox Then
                If .Value <> .OldValue Then
                    varBefore = .OldValue
                    varAfter = .Value
                    strControlName = .Name
                    [COLOR="SeaGreen"]'Build INSERT INTO statement.[/COLOR]
                    strSQL = "INSERT INTO " _
                             & "Audit (EditDate, User, RecordID, SourceTable, " _
                             & " SourceField, BeforeValue, AfterValue) " _
                             & "VALUES (Now()," _
                             & cDQ & Environ("username") & cDQ & ", " _
                             & cDQ & recordid.Value & cDQ & ", " _
                             & cDQ & frm.RecordSource & cDQ & ", " _
                             & cDQ & .Name & cDQ & ", " _
                             & cDQ & varBefore & cDQ & ", " _
                             & cDQ & varAfter & cDQ & ")"
                             
                    [COLOR="SeaGreen"]'View evaluated statement in Immediate window.[/COLOR]
                    Debug.Print strSQL
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings True
                End If
            End If
        End With
    Next
    
    Set ctl = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description & vbNewLine _
        & Err.Number, vbOKOnly, "Error"
        
End Sub

You'll need to modify it if you're using controls other than text boxes, but that shouldn't be too difficult. Make sure to read the article - it lays out the table you need to create as well as how to tie everything together and get it running.

Hope that helps!