Solved

combine table opinion

Posted on 2014-11-05
9
97 Views
Last Modified: 2014-11-06
Hi Experts,
We have an app with multiple tables that stores different types of notes.
now users are requesting to be able to view all of them at once.
would it be a good idea to combine all tables, and add a field to categorize the type of note, instead of having them in multiple tables the way they are stored now?
are there rules that determine what is right or wrong here?
what are the disadvantages of this move?
FYI- My BE is SQL and front end is Access ADP.
0
Comment
Question by:bfuchs
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40425279
How often they need to see the combined data ? if that's not very often, you can create a view which combines select query from all the tables
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40425289
Hi Aneesh,
I know there is a way of doing it, I just want to know if that is the right way, actually users are requesting to combine, and I want to know what rules to follow.
currently there is a form designed for filtering and its being used daily.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 40425305
For starters, do the tables have exactly the same schema, i.e. columns, data types?  
If no, then probably separate.
If yes, then what's the difference between these tables?  If it's just one column/property/whatever, then an argument can be made that they should all be one table in the first place, with a column that identifies which 'type' of note it is.
If that's not practical, then create a view that UNIONs all of the tables / columns, with an extra column that defines the 'type' of note.

Keep in mind that until you post the table schema, some sample data, and tell us the number of rows, all we can do is make educated guesses based on SQL design, and nothing to do with your specific database.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 4

Author Comment

by:bfuchs
ID: 40425496
Hi Jim,
do the tables have exactly the same schema, i.e. columns, data types?  
 If no, then probably separate
what happens if there is a slight difference, like some have one or two additional fields, would that be a reason to separate them?
thanks,
Ben
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 100 total points
ID: 40425644
> would it be a good idea to combine all tables, and add a field to categorize the type of note ..

The short answer is Yes. Don't forget an index on the NoteType field.

> what happens if there is a slight difference, like some have one or two additional fields ..

Nothing. Those fields will just be empty for the notes that origin from other tables.

/gustav
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 100 total points
ID: 40425698
I go with Gustav. I  am in favor  of combining them, with one field to group them as you suggested.

Advantages? You may have one form to input and save data.
Ease of maintenance if a change in table design is required.

Disadvantages? Trivial if any. In data entry, your program can disable the extra fields depending on the category.
0
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
ID: 40426242
I agree with Gustav also.  I always combine my notes into a single table if possible.  One advantage is that I don't need to keep creating new forms and reports.  The tricky part is the foreign key.  Since they could come from multiple tables, you can't enforce RI.  So if you have notes for customers, orders, and items, in the note table, you have the note, date, entered by, changed by, changed date type, and a foreign key.  The type field indicates which parent the note refers to.

I also don't allow notes to be changed or deleted except with higher authority.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 40426450
In general it's probably better to combine them if possible.  A few NULLable columns that only some of the note types use is perfectly fine.  The critical factor is not the data structure -- than can be worked around anyway -- but the efficiency of lookups and maintenance.

Thus, whether these tables should be combined depends.  Can you cluster the resulting combined table such that apps can do seeks (keyed lookups) for all common uses of all notes?  If you can't, every use will be forced to do a full table scan -- or have a covering index -- which will be very costly in terms of performance.
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 40427025
I see most of you are in favor of combining them,
Thanks to all experts for your valuable input!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL STANDARD CORE 8 38
Number of records returned on a form 5 25
changing page verifacation 1 29
Email Populated Report as PDF 2 13
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question