Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

[DB Redesign][MSSQL]Need Help About Spliting a single table data flow into multiple tables Expand / Collapse
Author
Message
Posted Tuesday, April 8, 2014 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:35 AM
Points: 1, Visits: 12
Hello,
I am a software developer using MSSQL databases in certain of my projects.
I Still have basic skills in SQL (Mostly using Access as my main databases) and I'm working about revamping one of my project because of some issues.

Before going strait to the problem a little bit of history of my project:

This project started in the middle of 2007 when I was a young developer (Final Exam presentation).
The project come with some compatibility issues: have a backward compatibility with the old system.
The data flow table (the one who have 99% of entries) was constructed like this:
Radio_ID (int,Null)
Date_and_Hour(datetime,Null)
Type (nchar(30),Null)
Latitude (nchar(30),Null)
Longitude(nchar(30),Null)

In the middle of 2009 A big update was done: support of finding the information about who was in some street.
The table ended like this:
Radio_ID (int,Null)
Date_and_Hour(datetime,Null)
Type (char(30),Null)
Latitude (char(30),Null)
Longitude(char(30),Null)
Street(varchar(255),Null)
Town(varchar(50),Null)

One Year later an another big update: creating zones nearly to be used for statistics or sectoring City places.
The table ended like this:
Radio_ID (int,Null)
Date_and_Hour(datetime,Null)
Type (char(30),Null)
Latitude (char(30),Null)
Longitude(char(30),Null)
Street(varchar(255),Null)
Town(varchar(50),Null)
Zone(varchar(255),Null)

The big problem is that the design is old and not optimized and high data flow makes the system to be slow after one month in big systems (like 50 to 100 radio located).

I have been reading some articles and books about SQL but I'm still locked in the Database redesign.
I have done a new design like this:
Histo Table (biggest data flow near 30000 inserts per day):
HISTO_ID(bigint,Identity) Primary Key -> Index Non Cluster?
Radio_ID (int,not Null)
Date_and_Hour(datetime,not Null)
Type_ID (int ,not Null) FK to T_Type.Type_ID
Latitude (float,not Null)
Longitude(float,not Null)
Street_ID (int,not Null)FK to T_Street.Street_ID
Town_ID (int,not Null)FK to T_Town.Town_ID
Zone_ID(int , not Null)FK to T_Zone.Zone_ID

T_Type Table (Number of entries under 100)
Type_ID (int, Identity) Primary Key
Type(char(30),not Null)

T_Town Table (Number of entries under 100)
Town_ID (int, Identity) Primary Key
Town(varchar(50),not Null)

T_Zone Table (Number of entries under 100)
Zone_ID (int, Identity) Primary Key
Zone(varchar(255),not Null)
T_Street Table (Number of entries under 5000)
Street_ID (int, Identity) Primary Key
Street(varchar(255),not Null)

The database may work in these conditions:
- Keep at least 3 month of data (Law limitation) the old design breaks the 4GB limit of SQL Express in two months
- Easy Insert/ remove / updates (making batch possible)
- fast search from all columns.
- Easy to add new information to the history (like speed, RSSI (info about radio RF reception))

In the last I don't know how to make the inserts in multiple tables in the same Time (transaction?)

For the select I know how to use Join

for the updates i have to do in two or three steps:
- Select of the column to be updated
- if the data doesn't exist-> create it
- update the line with ID from the select/ insert.

For information the data have to be shown like this:
Radio_ID;Date_and_Hour;Type;Latitude;Longitude;Zone;Street;Town
Post #1559502
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse