Hi All,
I have a design query
I have data of an entity which can be business logically divided on basis of some details. The raw description of the details
is as follows:
1 column primary key int 40 columns tinyint 30 columns varchar(100) 4 columns varchar(1000) 10 datetime columns
now, apart from insertion and updation, major chunk of my query (approx 50%) will be select query which will be based on 15 tinyint columns & 4 datetime columns and will be showing 1 primary key column, 10 out of 15 tinyint column, 2 out of 4 datetime column, 10 varchar(100) column and 1 varchar(1000) column.
I also have some queries which get some columns of the row corresponding to primary key specified. There are various queries like this fetching various different data based on business logical division.
I am using stored procedure to do all the required operations including select, insert and update.
Option 1 = Should i be vertically partitioning the table and then using Inner joins in the select query? Major Cost Factor = Inner Joins Alternates which may help = Make view of all the columns in all the tables required in the major select query. Don't know its impact on Insert, Update etc made on the base table. Insert and update won't be as frequent as select.
Option 2 = Should i be keeping all the details in 1 table and then doing the search on that? Concerns 1) What will be affect of complete table when the query asks for say 4 columns for a particular primary key as compared to the first option? 2) What will be affect of the non-used columns on the query running 50% of times? 3) What will be affect of the heavy varchar columns on the query?
Option 3 = Should i be keeping all the details required for 50% times select in 1 table and rest in other and then work on that? Concerns 1) It will make insertion, updation and selection of business logically data difficult as now they will be divided into 2 tables and everytime, a join will be required to get details of a logical division for a particular primary key
