What would be a database efficient approach? 1

[Katya85S]

In a database the data was stored for customer satisfaction records. Now users want to have the same table structure, accept for Dissatisfaction records. There are two ways to accomplish this: either add a column specifying “Dissat” or “Sat” records to the existing table or create a new table identical to the existing one that will collect “Dissat” records. It seems to me that the 2-table approach should run queries faster, due to fewer numbers of records per table. But at the other hand there could be more advantages of having all the data in one table. Which approach is considered to be more efficient?
Thank you all in advance.

 

[JohnDTampaBay]

It also depends on how much existing code refers to the table. If you have a lot of queries/reports etc. accessing the table, they would need to be updated to filter on the new column to get proper results.

SQL Server can easily handle millions of rows in a table with great efficiency given proper indexing.

Create a test environment where you can try both methods. If the table is already huge, try a subset of rows. Check the execution plan and other statistics to see which is most efficient for your purposes.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[vongrunt]

That information is basically a flag? Yes/No/unknown and nothing else?

One table then.





------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[Katya85S]

Hmmm. John's point on reports and queries was right. It does have some number of reports nad queries, which i would have to correct applying 1-table + flag column approach. But why do you Vongrant say 1-table is a right way to do so?
Thank you guys.

 

[druer]

I think I'm lost on the whole idea that there is anything to distinguish a Customer Satisfaction verse Disatisfaction record. A Customer Satisfaction record in nature contain values regarding a customers level of satisifcation:

What do you think of our quality? (1, 2, 3, 4, 5) where 1 means we suck and 5 means we were great etc.

A record with the value of 1 is the same as a record with the value of 5.

The only thing that I can possibly imagine (without any further input from you ...) is that perhaps if customers respond with 1 (we suck) they then get another set of questions that don't apply if customers respond with 5 or something. If that is the case then the data related to the fact that the customer wasn't satisfied would mean another table since it would only relate to "unsatisfied" customers.

Just my thoughts,
Dalton

 

[Katya85S]

Table structure would be the same for both 'Sat' and 'Dissat' Reasons: Date, DeptNum, Operator Num, Sat/Dissat Reason, Description. Though reports, forms, and Queries i would have to perform separately for either Sat or Dessat records. So if storing new, dissat, data to teh existing table i would have to add a new column that will have '1' for 'sat' and '2' for 'dissat', etc.
Applying this approach i would have to make changes to already existing reports/forms/queries in accordance with this new field. So, perhaps, i should stick with 2 different tables approach... Unless it might be inefficient in some other ways which i so far cannot see...

 

[JohnDTampaBay]

I believe vongrunt and druer make the same point: other than "quality" of values in the columns, nothing distinguishes the types of records. I was considering retrofitting existing queries that assume the data in the table only applies to satisfied customers.

Strictly speaking from a data perspective, one table is sufficient. However, we rarely have the luxury of considering only the data. We must consider existing interactions with the data and the resources needed to maintain current operations.

Just my 2c. Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[druer]

So basically you are saying that up to this point those who called and were satisfied were recorded to a table with basically a testimonial and date, and that calls where the customer complained were just dropped on the floor and not recorded at all in the database. Interesting way for the Customer Service Department to run the business. Now when I call companies and complain and they say "Nobody else has ever complained about this" I'll know that really means you aren't the first, we just never recorded the previous ones so that we'd know when a pattern was forming.

I don't think that there would be a critical reason to do 1 or the other in your scenario. No doubt there is work to change everything that exists now if you put things in the same table, but consider the fact that you also have to create all new user screens and reports to handle the "disatisfied customers". If you simply add the field then you change the existing screen and add a checkbox field "customer was happy ... customer is unhappy." But if you add a new screen then the end users have to choose the right screen on the menu, perhaps before they understand whether it is a happy/unhappy customer.

 

[Katya85S]

Thank you John..
Thank you guys..

 

[jegaby]

One more thought to consider. If you want to put everything in a single table, without having to update existing queries/screens, you could do this:

1) Let's say your table is now called SatisfiedComments. Add a column to distiguish between satisfied and dissatisfied entries and then rename the table to something like CustomerComments.

2) Create a view that only selects the satisfied entries and call it SatisfiedComments.

Now your existing queries, etc. should still work.

 

[Katya85S]

Thank you jegaby

 

[druer]

Very excellent solution jegaby. "If it was a snake it would have bitten" the rest of us as they would say.

 

[JohnDTampaBay]

Well done, jegaby. I was overthinking the problem and couldn't see the simple solution.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[DatabaseDude]

Question, apologies if it was addressed I hadn't seen it ... is there the possibility a customer would have multiple records (one where satisfaction was expressed, and another for dissatisfaction)?