[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 112
  • Last Modified:

combine table opinion

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
bfuchs
Asked:
bfuchs
5 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
bfuchsAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
bfuchsAuthor Commented:
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
 
Gustav BrockCIOCommented:
> 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
 
hnasrCommented:
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
 
PatHartmanCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
bfuchsAuthor Commented:
I see most of you are in favor of combining them,
Thanks to all experts for your valuable input!!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now