Geek asks and answers

Should I put additional record properties in a separate table?

I have a table of about 1000 records. About half of them will use a set of fields containing certain characteristics. There are about 10 corresponding fields. The other half of the entries do not need completed information.

This table is central to the database and will occupy the bulk of the operations. Although there are only about 1000 records, this is not so much.

The hardware on which the database is stored is old and slow (a spinning hard drive is not an SSD ...), so I want to have a fairly optimized structure to make the most of it. Obviously, increasing the size of the database alone due to empty fields is not a serious problem, but if it slows down the queries, then this is not good.

I think I should describe the setting. There is currently an Access 2007 client and an Access backend, although the backend will be migrating to the SQL server soon. The backend is currently located on the main server rack, but when upgrading to SQL Server it will receive its old server rack.

So should I make a separate table to store the aforementioned feature set or leave it as is?

+5
source share
3 answers

The overhead of entering markings of optional fields in a separate table, and then using the join, is not very useful for managing dimensions or data. Especially if it is 1 to 1 , as in your example. For size, optional fields will be NULL do not greatly affect you. And yes, 75% is a good random threshold when you should start moving things, but even then you don’t actually normalize anything by moving optional fields (if they are 1-to-1 with a record, and you will always select it together with the master record).

: , ... , . Access 2007 .

, , , where/having/join.

+3

, , , . , , ( "-" ) , (-) - .

, , . . , : .. , - - .

. . , .

+1

Vertical splitting makes sense for a large dataset to make the cache more efficient. 1000 lines do not qualify as "large" even on fairly old equipment.

So, if there is no other reason to reverse engineer this table (you are not merging the search , right?), You should go.

0
source

More articles:


All Articles