I have a database in two versions, but with the same structure and data: one for a website (SQL Server and VB.Net) and for an android app (Xamarin and SQLite). I have doubts about the structure of a part of the data.
I have a list of objects (rows in my table) with a long list of associated fields (Length, Weight, Color, ...) as columns. The number of columns is higher than 100, and not all columns are filled for all objects; in practice there are a lot of null values.
My idea was to split the table into 3 new ones: the first containing the always present main feature, the second with the less frequent columns (in the rows) and the third something like a Pivot.
- Table 1: ObjectID, Name, Barcode, …
- Table 2: FeatureID, FeatureName
- Table 3: ObjectID, FeatureID, FeatureValue
Is the select query performance the same? Are there any differences between SQL Server and SQLite? Do you have other solutions?
1条答案
按热度按时间mwkjh3gx1#
The general considerations about designing databases are :
This leads to normal forms ....
Each time you violate those principles you will have peformances problems. Because thoses technics have been created to reduce data access !
In your single table with 100 columns you will never be able to optimize the data access with indexes, because the number of indexes to do so is over 100! (factorial 100).
The exact number of indexes you should have to be sure that any query will be fast with BTree indexes is :
where n = 100