Should I create a view or another table?

Question

Firstly, as a disclaimer, I'm learning a little about SQL/LINQ indirectly through C#. I'm very green in both really. I've quickly grown tired of using sample databases full of data and doing queries set in the most simple of situations. The information is presented this way because the focus is on C# and visual programming and not the hairies of SQL or even LINQ for that matter.

Even so, I decided to convert a simple application I wrote using text (CSV) files for storage. The application keeps track of three possible payables where 0 - 3 records will exist for a given date. I was able to create the database and separate tables for each contract and build an application that inserts the existing records into the database using LINQ to SQL classes.

Now I have another application that is used to add entries via a contract calculator or directly through a BindingSourceNavigator and DataGridView. Each table has in common four columns - Date, GrossPay, TaxAmount, and NetPay, Date being the primary key. I'd like to view the records by date where I have TotalGross, TotalTax, TotalNet, and a column for the GrossPay each contract table for that date.

What would correct approach to this - a view, LINQ query, separate table, or other? It seems a table would be the "easiest", at least in terms of my ability, but seems like an unnecessary copying of records. I tried to "link" the tables but no other column is guaranteed to be unique or primary.

Any suggestions would be great.

Clarification:

Three tables have the format:

** Each table has specific data used to calculate the common columns based on contract type

I would like to view all records "grouped" by date such that each are represented like:

** "Total" columns are sums of the respective columns of all records sharing the date.

** One or two of the "Table(n)Gross" may be zero

Its a bit difficult to understand what you are trying to do in this question, Can you clarify it? — dcarson, Commented Jun 5, 2013 at 3:31

dcarson · Accepted Answer · 2013-06-05 03:40:31Z

0

I think you are asking if you can select records from three different tables by date for the columns they have in common?

If so, you need to do a union.

In your case it may look something like this in SQL. note that I have made a dummy column to denote the source of the record (which you may want)

SELECT Date, GrossPay, TaxAmount, NetPay, 'Table1' as Source FROM Table1
UNION
SELECT Date, GrossPay, TaxAmount, NetPay, 'Table2' as Source FROM Table2
UNION
SELECT Date, GrossPay, TaxAmount, NetPay, 'Table3' as Source FROM Table3
WHERE Date = '2013-05-05'

I wouldn't bother with a view and definitely don't replicate your data with a seperate table.

answered Jun 5, 2013 at 3:40

dcarson

2,8531 gold badge27 silver badges34 bronze badges

Thanks for your answer. I don't have a problem bringing entire tables together based on some constraints. What this ends up being is individual listings of the three tables. Since asking I've seen other examples of inserting into multiple tables. Is your advice not to do so based on large data sets? There would be at most, and rarely, four records inserted per day totalling <1600 per year.
– ChiefTwoPencils
Commented Jun 5, 2013 at 4:20
In terms of numbers of records, that is negligible, however replicating data is a bad idea.
– dcarson
Commented Jun 5, 2013 at 4:30

Add a comment |

Collectives™ on Stack Overflow

Should I create a view or another table?

1 Answer 1

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related