Solved

combine table opinion

Posted on 2014-11-05
9
95 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 3

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
 
LVL 3

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
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.

 
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 34

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:ScottPletcher
ScottPletcher 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 3

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

910 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now