Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


[DB Redesign][MSSQL]Need Help About Spliting a single table data flow into multiple tables


[DB Redesign][MSSQL]Need Help About Spliting a single table data flow into multiple tables

Author
Message
daniel.dosreis
daniel.dosreis
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search