_ATOM_SITES vs _ATOM_SITE

[BobHanson]

Q: How is _ATOM_SITES useful? In particular, if _atom_sites.structure_id is given, does that somehow suffice for _atom_site.structure_id not be present even though _atom_site_moment.structure_id or _atom_site_aniso.structure_id are present, because _atom_site.structure_id can be inferred in some way from _atom_sites.structure_id?

[jamesrhester]

In thinking about this it might be helpful to consider that the only way to relate any item A and item B in the relational model is either for them to be columns in the same table, or for the tables containing them to have columns that are linked to one another. That's it. So items in ATOM_SITES and items in ATOM_SITE have no relationship to one another unless there is some link between columns in one and columns in the other. The fact they're both in the same data block has been used in the past to create a relationship, which in relational terms is like saying there's a hidden extra column in all categories with the data block name in every entry.

Of course the definition text says (and the data names themselves suggest) that ATOM_SITES contains information about the atomic sites in general, but those words mean nothing to the computer. So we have to link the categories in a way that is consistent with how they are used. We could create an _atom_sites.id and an _atom_sites.structure_id and point to _atom_sites.id from new data name atom_site.atom_sites_id. Or we could link both to _structure.id, which we've done, as _atom_sites.id would only have one place it was used. But we can't do as you suggest and link one to _structure.id and do nothing with the other, as that other is then left floating in the void with no connections to anything.