SQL Server Project.
Hi
I am into a sql project. The project is scheduled to start during sept and is for a month duration. I am a beginner in sql, however I have experience in control system domain.
I can execute basic sql that are required, however this is for the first time I am going to be a part of full fledged sql project.
Can anyone help me in clearing few of my doubts.
Below are the details of the project.
******************** Brief about the project ********************
Live data from ground level instruments is coming via a scada application (which acts as a front end software) needs to be dumped on sql.
There are 8 machines from which data needs to be taken. Hence it has been decided to have 8 tables, 9th table will have consolidated data of all 8 tables.
After the data is collected, a report needs to be generated, one individual report for each machine. And a consolidated report for the all the machine.
We also need to have different authenticated levels for different users, and based on their login the user information ll be captured.
******************** Tools/Platforms that ll be used ********************
SQL SERVER 2008, SSRS Reporting tool
******************** Approach ********************
During the meeting with the client, it was decided first we ll make the database ready and deploy it on the production plant, thereafter we ll optimise the data once the needs arises in future. However I have explained this is not the correct approach as the data ll grow in size with a short time, as it has been found out during first stage analysis.
The data generated is huge, around a 1000 rows per machine/hr. So roughly speaking the calculation goes like (1000*24hours) = 24000 rows per day per tble. So in a months time the data will be very huge.
So I explained to them the contraints that we ll encounter in the near future & I have been asked to come with an alternate solution.
******************** Design of the table ********************
I am planning to first address the database design needs. Any suggestions for the improvemnt of my points are highly appreciable. Please excuse if anything silly, as I am executing full fledged db project for the first time. Though I have seen database servers during my earlier projects.
******************** A small note ********************
Based on my experience to earlier plants, I have seen when the data grows exponentially beyond , say 500 mb. The retrieval time increases.
I have seen people struggling to retrieve data which consumes a good 1 hour +. However my question is it common for the system to take soo long to retriev the data when it grows to such an extent?
I am asking this because even a days data that needs to be displayed the next business day comes around 192000 rows. And the consolidated report needs to have atleast a months data which is very huge.
******************** Coming back to the design part ********************
Each table needs to have the above columns, each tbl is addressed as Machine1 to Machine8
SlNo
OperatorName
OperatorID
Dept
MachineName
MachineID
PowerGenerated
AlarmPriority
AlarmsAcknowledged
DateTime
I am planning to have a separate table with operator name, operatorID and dept as primary key and to call it in another table through foriegn keys. I hope it will be of help to a suffiecient level to keep the data from growing.
I am plannig to have the last table as a view of the 8 physical tables.
Does having indexes on the table help to optimise the exectution time, I am not sure how indexing ll help to reduce the execution time.
Can anyone suggest if the above approach is good or is it lacking any key points.
Also can anyone help me in optimising the data for faster retrival time.?
Is there anyway to store the data, say 6 months data, which is readily accessible than to just maintain backups on external hard disks
If you require any more info I would be happy to share.
Awaiting your replies