What is better: single table with many null data or two tables without nulls?

[Javier Hernández]

I'm attempting to redesign some tables in a database and I have run into a problem.



There is a table that contains information about individual items. There is information that matters only to certains types of those items which comprise about 40 % of the records. For these records, the additional information comprises about 30 fields but it is unique to each one of them.

If I use a single table and add the fields that matter to those items only, I will have a table with a lot of null cells. If I create another table (let's call it table B) for these data I don't have nulls, but as far as I know even if there is a 1-to-1 relationship Access will treat the join as a 1-to-many, meaning that forms that handle those items will become a lot slower.

Which is better?:

1) Single table, even if there is a lot of null cells. How is data stored? Is a lot of disk space wasted this way?
2) Two tables, 1-to-1 explicit relationship and single forms. Are joins faster if the relationship is specified?
3) Two tables, 1-to-1 explicit relationship, a form for table A with a subform within for table B, even if only one A record relates to one B record. A little more work, but if it has the better performance, I'm willing to do it.

[June7]

It is a balancing act between normalization and ease of data entry/output.

1. No, don't think a lot of disk space is wasted, besides, memory is cheap these days

2 & 3. You mean in the relationship builder, no don't think that helps queries run faster, just automatically sets the link when tables pulled into query builder and allows cascade delete and update.
Two tables would have to use a form/subform for data entry/edit.

[orange]

Research supertype and subtype for some info on this sort of topic/issue.

Here's a small tutorial http://sd271.k12.id.us/lchs/faculty/...dd_s04_l01.pdf to help

Here's more http://technet.microsoft.com/en-us/l.../cc505839.aspx

[Javier Hernández]

Thanks for the response. I didn't knew how to express the problem, but the supertype / subclass terms were exactly what I was dealing me and put me on the right track. I wasn’t completely satisfied with the info, though, since it didn’t detail how to implement the classes after modeling the system. Fortunately, I got the book Database Systems – A Practical Approach to Design, Implementation and Management by Thomas M. Conolly and Carolyn E. Begg (highly recommended) in my hands and found something of substance. After setting the technical terms, the authors offer a guide on how to create the tables, although they do note that the rules aren’t final and context is still king. I post it because it is not too long and, given the amount of times it tends to be an issue, it may help.

Obligatory participation, non-disjunctive. (The superclass must have a subclass. It may belong to more than one subclass).
One unique table, with fields which signal if the element belongs to each subclass.

Optional participation, non - disjunctive. The superclass may not have a subclass. It may belong to more than one subclass.
Two tables: one for the superclass and one which encompasses all the subclasses it may belong.

Obligatory participation, disjuntive. (The superclass must have a subclass. It can’t belong to more than one subclass.)
Many tables: one for each combination superclass / subclass

Optional participation, non-disjuntive. (The superclass may not have a subclass. It may belong to more than one subclass.)
One table for the superclass, one table for each subclass.

I'll be following these directions for now, since they make sense.

[orange]

Glad that I was able to help, even if only to point you to the topic. And thanks for getting back with this info.

Here's a link to a previous post that may complement what you are finding.

Other EERD super/sub type info.

Good luck with all your projects.