How to design SQL table like a abstract java superclass object?

Question

I have an entity table with:

entityId       indexType (String)
1              employee
2              supplier

I am trying to create a table for the name of the entity;

nameId      fk_name_entityId       firstName       lastName        
1           2                      johnny          appleseed

The problem or flaw I have with this design is that not some entities types such as suppliers and commercial ones dont have firstNames and lastNames. They have only one name their business name.

I am using this mySQL database in a java application. So I can just create an abstract superclass.

I do not want to add a businessName column as I feel like thats bad design, because there would be a lot of empty columns.

-- bad design -- 
nameId      fk_name_entityId       firstName       lastName       businessName      
1           2                      johnny          appleseed        
2           8                                                      Apple

My question is: What is the best way using mySQL to do this?

hmm another word for surname is title, is it not? why not use title and leave the first name empty for companies (or use name and surname and leave the latter empty) — inarilo, Jul 13 '17 at 23:37
@inarilo Those look like just individual examples. There may be lots of other columns that are specific to businesses or people. — Barmar, Jul 13 '17 at 23:38
Possible duplicate of [How to design a product table for many kinds of product where each product has many parameters](https://stackoverflow.com/questions/695752/how-to-design-a-product-table-for-many-kinds-of-product-where-each-product-has-m/695860#695860). — Bill Karwin, Jul 13 '17 at 23:39

score 0 · Accepted Answer · answered Jul 13 '17 at 23:37

The way this is often handled is with a generic attribute-value table:

nameId  fk_name_entityId    attr_name   attr_value
1       2                   firstName   Johnny
2       2                   lastName    appleseed
3       8                   businessName Apple

A well known example of this is the wp_meta table in the WordPress framework.

In some designs, instead of putting attr_name in this table, it's a foreign key to another table that lists all the valid attributes.

How to design SQL table like a abstract java superclass object?

1 Answers1