At our site we have a mix of one-to-one and one-to-many tables based on a numeric ID.
In our case, we have a "core" table that identifies each person. We have classes of supplemental information that MIGHT apply to a person. Some of them are one-to-one, others are not. In some cases, the one-to-one is based on the fact that, though a person and a position (job, billet) can co-exist indepently, when the position is filled, it is one-to-one with a person. PK/FK issue, of course, but it IS a one-to-one relationship.
Our applications are segmented enough that we only have to join the main table to one or two of the sub-tables at a time when doing the processing we do. Shorter table size means faster access. Given that we have some 240 tables with entirely too many fields to be completely concatenated, we need to work on subsets at LOT.
As you point out, security is also an issue. Some of our tables have more stringent security than others. And of course, if user X has no rights to one of the tables, the app bombs before he sees anything he shouldn't have seen.
Since this is a U.S. Government site, you might take heart to note that we agree with your views on having one-to-one tables.
As to Pat's suggestion, I both agree and disagree. If it happens that you truly don't have the information, the table containing that class of information could indeed have no record at all for the given ID. However, sometimes you need a place-holder record for obscure procedural or regulatory reasons. So the answer as to whether you even need the record in the second table is, "It depends."
If your application can correctly handle null fields coming back from a join query as Pat suggests, then omit the record entirely. Otherwise, populate a record with the ID and a bunch of empty strings. It simply depends on what your code will accept.
Having said that, I would always opt to save space by allowing the extended-attribute tables to just omit the record entirely and use the properties of the join to allow me to read it anyway. Which means I would code my application with that option in mind right from the get-go.
