Database design shortcoming?...or not?

[igavemybest]

I have a question, and since I am not a database guy maybe someone more skilled could answer this for me.

When doing custom imports, etc... if someone adds a product in the database they must create a corresponding row in ac_CatalogNodes. But when someone wants to delete a product they must remember about the record in ac_CatalogNodes to delete it too. Same with categories. Why isnt this done with foreign keys, and then records from ac_CatalogNodes deleted automatically.

One more example. ac_CategoryParents does not have foreign key for ParentId, so I can write 938293482039482 in this column and there is no category with this ID.

Just trying to understand the reasoning, to better understand the functionality. Any help is appreciated

[mazhar]

In software solution some time to you may need to make some decisions to make deviations to standard rules in order to achieve flexibility. ac_CatalogNodes is a special table and every entry of catalog node table represents a catalog object. There could be four different catalog objects categories, products, webpages and links. So every entry in there is associated to one for the four tables. You can't enforce constraints because you can't map four foreign keys on on column CatalogNodeId. Its will be the code responsibility to make sure to enforce this kind of constraints. If you are using AbleCommerce API then you don't need to worry about this. It will handle it automatically.

[igavemybest]

Ahhh..ok. Makes sence. Thanks for the insight

[victor_k]

But there are other solutions. For example you can create one column for each object type (categories, products, webpages and links) and create corresponding foreign keys for each column. Then, if you want to work with one column you can crate additional view with current ac_CatalogNodes structure. This way can be even fast. Code should not be held responsible for the consistency of the database.

And what about ParentId in ac_Categories?

Regards.

[Logan Rhodehamel]

One more example. ac_CategoryParents does not have foreign key for ParentId, so I can write 938293482039482 in this column and there is no category with this ID.

That is not a normalized table. It is a custom index that is necessary to support certain features within the product, especially ones that involve inheritance of categories.