CODEWITHSUNDEEP
sqldatabase-design
ipohfly
ipohfly

Reputation: 2009

C# - Table structure for child entity

Sorry if this sounds noob, this is something related to database design for inherited classes.

I have currently 2 type of user for registration on my system, member and staff. They both share some same property (i.e. name, dob, contact no) while possessing some unique property for their own usage (i.e. membership no for member, staff position for staff). Currently they both are stored as 2 different tables in the database and have no relation with each others.

I'm thinking now to create a entity called Person with all the common property while having a class call Member and another Staff that both extends the Person class in the system. However i'm having problem designing the database. Should there be a table called Person with a type property to denote the type of the person, or i should stick with the existing design where 2 tables that separate them?

Upvotes: 1

Views: 90

Answers (1)

Krzysztof Kozmic
Krzysztof Kozmic

Reputation: 27374

It depends.

If you're not going to do polymorphic queries, I'd stick to the design you have right now.

This sort of design is called in literature "table per concrete class"

On the other hand if most of your queries are going to be like "give me all the people in the system regardless of whether they're a member or staff" you might want to put them all together into a single table called Person.

This sort of design is called "table per class hierarchy"

Obviously this mandates that all columns corresponding to properties of your inherited classes would have to be nullable in the database.

If that is not acceptable (I'm finding it hard to believe it would, but...) and for completeness you may end up with a three-class, three-table structure, which is unsurprisingly called "table per class"

There's no hard and fast rule for this, and while I generally tend to work with systems where table per class hierarchy makes the most sense, it may or may not be the case for you.

Upvotes: 1

Related Questions