What is best practise for implementiong overlapping inheritance in databases

Question

Let suppose, I have a parent table named as 'Name' and two child tables named as 'MaleName' and 'FemaleName'.

But some names are such which both Male and Female can have, (it is more common case in asian countries/names). So do I need to store such names both in MaleName and FemaleName tables. But that will trigger redundancy and dont feel right, or I add a third table as 'CommonName' to hold such names, again that dont feel right.

Then, what is best practice to serve the problem?

• zarruq: I think in this specific case, only one table having Boolean column(s) for Male/Female/Both flag will suffice
• Cellman: That is alternative design approach than implementing inheritance. It has its pros and cons. If I want to stick to inheritance manual way, then what is way to go
• reaanb: Use an FK constraint. Redundancy isn't a problem in itself, only uncontrolled redundancy that creates a risk of inconsistency.

Answer 1

If it is not more complicated than your example, then having more than one table in the first place is redundant, simply have two columns on your name table to mark if it is Male, or Female, e.g

CREATE TABLE Name
(
 Name VARCHAR(255) NOT NULL,
 Male BIT NOT NULL,
 Female BIT NOT NULL
);

If you absolutely need MaleName and FemaleName as objects, then you can always create views:

CREATE VIEW dbo.MaleName
AS
SELECT Name
FROM dbo.Name
WHERE Male = 1;

Assuming this is not as simple as your example, then a fairly common way to deal with this is with an associative entity (junction table), so you would end up with 3 tables, something like

Name (NameID (PK), Name)

Gender (GenderID (PK), Description)

NameGender (NameID (PK, FK), GenderID (PK, FK))

Finally, if you are set on the inheritance approach, this shouldn't cause redundancy if you are doing it properly. Any attributes common to both Male and Female names should be stored in the parent table, therefore any information in both child tables would not be redundant