Solved

combine table opinion

Posted on 2014-11-05
9
93 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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.

705 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

19 Experts available now in Live!

Get 1:1 Help Now