Database Design Advice

LenCepeda
LenCepeda used Ask the Experts™
on
Hello - Please let me start by mentioning I am completely new to MS SQL server and database design.  I have read a DB design book and looked at a few online tutorials.  

Here goes.  I am attempting to import a file (.csv) from another system on a nightly basis into a new MS SQL 2012 DB (Enterprise Ed).  Once the CSV is imported and formatted as a table (Sales) it will be related to one other table (CustomerID) within the DB.  I would like the CSV Import to append new data to a pre-existing table and overwrite any records within that table that have like "SalesID" number.

So far, i setup a MS Server 2012 VM, installed MS SQL Server 2012, Created the DB. successfully manually imported a CSV into the DB to create the initial SALES table and now I am researching solutions that will allow me to import newly created CSV on schedule.  It looks like SSIS / Bulk Insert should do the trick so i installed those feature and began to research next steps.

Once the DB is complete, it will be connected with MS Excel Powerpivots that users can access and refresh.  I have some experience establishing those types of files so i think we will be fine once we work our the basic DB.

1.  Am I on the right track?
2.  Should the imported CSV's go directly into the pre-existing table or should that data get imported into a new table and merged using another process?
3. Once the data is merged in the "master" SALES table, should it get copied elsewhere to allow the Powerpivot connection or is it ok to allow the Powerpivot to connect directly to the "master" sales table

Thank you, any advice would be greatly appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
1.  Broadly, yes.  Specifically, no.  You skipped the part when you do a true design process to get from a CSV file to normalized relational tables.  The correct design will almost never be to load a spreadsheet into a single table.  At least some code/lookup tables should be needed.
2.  The CSV import should go into a staging table, not the main data table(s).  Staging tables are extremely common.
3.  It's ok to allow Powerpivot to use the main table (for reads only).  If necessary, you can use snapshot isolation later to speed it up.

Author

Commented:
Thank you, Scott.

I guess i jumped into this a bit prematurely without exactly having a well thought out plan or ER Diagram but sometimes I feel I pick up new things by just trying (and failing).  In any case, I was able to create a Data Flow SSIS package and successfully import the CSV into an existing table or should i say staging table now.  The next step will be to figure out a way to schedule a job to run the data flow package on a regular schedule (appending new SALES data to the staging table and modifying/deleting existing SALES data based on SALESID).  Is this what you mean by a code/lookup table?  If so, can you please elaborate?    Thank you and best regards.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
To do a design, I would have to see the column names with data descriptions of what's in the CSV.

"Codes" are if some char strings are being repeated in the CSV that could be encoded or other char string performance changes.

For example, if a "status" column in the CSV had "Invoiced", "Paid", etc., a numeric code would replace that string in the table and the actual string would be moved to a computed column or separate table.
Olaf DoschkeSoftware Developer
Commented:
The important part of Scotts previous answers is your data import is just into a staging table. Merely there to receive the data. Afterwards you would really do the merge of that data with already present data, and you have the literal MERGE command for that matter, it can update records with already existing SalesID and insert new ones.

Bye, Olaf.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial