CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-design
Cellman
Cellman

Reputation: 135

What is best practise for implementiong overlapping inheritance in databases

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?

Upvotes: 0

Views: 254

Answers (1)

GarethD
GarethD

Reputation: 69769

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

Upvotes: 3

Related Questions