Should I put optional record properties in a separate table?
database, database-design, ms-access, ms-access-2007, normalization
Solution
The querying overhead of putting the optional fields into a separate table and then using a join doesn't provide much benefit to size or data managment. Especially if it's 1-to-1 like in your example. For size, the optional fields will NULL don't affect you much. And yes, 75% is good random threshold for when you should start moving things out but even then, you're not actually normalizing anything by moving out the optional fields (if they are 1-to-1 with the record and you will always be fetching it along with the main record).
Worth noting: With most DBs, getting large rows in single queries is better than several small queries...in case you later have the urge to get the optional data in the 2nd table in a separate query. In Access 2007 this may matter less though.
And regardless of whether or not you move those optional fields out, add indexes for those fields which you may use in a `where`/`having`/`join`.
Problem
I have a table of about 1,000 records. Around half of them will utilise a set of fields containing certain characteristics. There's about 10 relevant fields. The other half of the records won't need that information filled in. This table is central to the database and will be taking the bulk of the operations. Though at only around 1,000 records, it's not much. The hardware that the database is stored on is old and slow (spinning hard drive not SSD... ) so I want to have a fairly optimised structure to make the most of it. Obviously the increased size of the database alone due to the blank fields isn't a major concern, but if it's slowing down queries then that's not good. I guess I should describe the setup. Currently Access 2007 client and Access backend, though the backend will soon move to SQL server. Currently the backend is on the main server rack, but when moved to SQL Server it will get its own older server rack. So should I make a separate table to store the aforementioned set of characteristics, or should I leave it as is?