Menu
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?
200
You should put an attribute in a separate table whenever you expect that one person could have multiple of that attribute. Otherwise, there's not much reason to separate it, and there can be some conceptual overhead in doing so.
A table has records (rows) and fields (columns). Fields have different types of data, such as text, numbers, dates, and hyperlinks. A record: Contains specific data, like information about a particular employee or a product.
Data organized into a table is more useful than unorganized data. A table helps you recognize the relationships among data and evaluate the data more effectively.
A record is simply a set of data stored in a table, for example, a customer record. A record in a database is an object that can contain one or more values. Groups of records are then saved in a table; the table defines the data that each record may contain.
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.
181
My impression from what you've said is that you should use separate tables. The dependencies you want to represent and the needs of data integrity ("business rules") should determine which table(s) any attribute goes in.
In your case it sounds like you have two kinds of facts to be represented. Those fact types have distinct sets of attributes and therefore they belong in different tables. If you combine two different fact types into one table and make one set of attributes nullable then you could compromise data integrity: i.e. by permitting values for some attribute when the business rules require no such value and by allowing a value to be absent when business rules in fact require it.
For a more formal way of answering this, see Fifth Normal Form and the Principle of Orthogonal Design. If you aren't already aware of those design principles then you should familiarise yourself with them.
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
