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

• daniel.dosreis

  Grasshopper

  Points: 11

  More actions

  April 8, 2014 at 8:14 am

  #284288

  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