Solved

combine table opinion

Posted on 2014-11-05
9
98 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 50

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 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