Database with many one-to-one relationships

[Lou_Reed]

I have a database with a lot of one-to-one relationships in the tables. I think that such a db can be simplified
so that most of these tables can be integrated into the design with no loss of information or database
capability.

Is there some article put out by Microsoft on this?

I think that any table database design that has so many one-to-one relationships is not very professional.

Any help appreciated. Thanks in advance.




Lou Reed

[JoeM]

I think that any table database design that has so many one-to-one relationships is not very professional.

The professionalism of a database design would not be judged on the number of one-to-one relationships, but rather whether or not the tables are normalized.

If some of your one-to-one relationships also have the same (or close to the same) amount of records in each of them, it may be a sign that the data could be put into a single table, if it makes sense to do so. For example, if you were tracking address, it wouldn't make sense to have one table for a person's city, and a separate table for their zip code. You would just want one single address table.

But you have to be careful not to go too far in the other direction. I have seen people try to do everything in one table. If you have a lot of "blanks" in your table, it is often a sign that the columns with all those blanks might be better served in their own tables.

Here are a few write-ups on database design. Play special attention to (at least) the first three Rules of Normalization. If you follow those, it will guide you in how you want to efficiently design your tables.
https://support.office.com/en-us/art...4-bd4f9c9ca1f5
http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

[June7]

IMO, it is a balancing act between normalization and ease of data entry/output. I have one database that violates many 'rules' but it accomplishes what we want, which is to produce reports of laboratory test results for distribution. We do very little statistical analysis with the data. Simply run tests and report results. I have a primary table for logging in sample info and 1-to-1 related tables hold the test data. We have about 100 tests available for association with each sample (usually no more than 6 are selected). The data collected from each test differs greatly from other tests. A single table could not hold all the fields, even some of my queries come close to the 255 field limit. Further normalization would complicate structure of forms and reports so it was at this point we decided 'far enough'. Yes, lots of blank fields but this is tolerable. This structure was modeled on DOS dBaseIV version that had been running for 20 years. The only situation this structure caused me grief over is building graphs. I had to employ UNION queries to accomplish.