Database design pattern for inheritance / overrides

Question

I am looking for a good way to implement - default characteristics for an object and override the defaults in a database.

I have a table called Products - that maintains the list of products One of the products is mailbox and the value for the attribute color is blue I need the ability to specify that mailbox is red when the country is UK Rather than create one row for every country I want the ability to say if there is no entry for the country use the defaults.

I have multiple products in the products table

TIA

Answer 1

If you are using a DMBS that has triggers this might be a good time to use them.

I would assign a default value for the color column but have a trigger that checks the country on insert and can override that default value as needed.

Answer 2

How about this solution:

Products(#product_id,...,color)
ProductLocalization(#country_id,color,...)

You can also create a view to simplify the data access:

CREATE VIEW LocalProducts
AS
SELECT p.product_id,...,country_id=[default],p.color
FROM Products p
UNION ALL
SELECT p.product_id,...,pl.country_id,pl.color
FROM Products p, ProductLocalization pl

The [default] placeholder should be a default value depends on the datatype of country_id. It can be a 'default' for varchar type or a 0 for int type (and the valid id of actual countries should be in range 1 to N).

When you need to localize products for a country, you will just add a record of product features into table ProductLocalization with a specific country_id.

Answer 3

The question is very open-ended. Which DBMS are you using? Do you have any say in the design of the tables in play outside the default value? If so, I would suggest thinking up a new design since your associations do not allow the constraints you need to enable. No matter what you do, it sounds like you are looking at placing logic in default values, which is a bit unorthodox.

My suggestion is think about placing this logic in the application or, worst case, in a trigger like Abe suggested.