0

I system requirements I have are as follows: In my app,as far as person entities, let's say I have users, adults, children, parents, friends, artists, customers, businesses, employees, event participants. The App it is basically like a social media platform with e-commerce functionality (ie: users create an account and can connect with "friends" or other people, can go to virtual "businesses" (that have virtual "employees") to buy, sell goods as "customers", or go to virtual house to chat, or watch virtual media content (as "event participants") provided by "artists".

Everyone must create a "user" account before doing on the app or becoming anything else. There are adults and children. A child must have a parent account first they can connect to. So I have a user entity with user_id as pk. Any other person entity will have this user_id. So a user creates an account, then builds a house, connects with friends and that's it. Artists will use the system as a monetized activity and after they create their user account, will create another artist like account and post their media to be accessed by other users. Businesses will use the app as a monetized activity. For example, a business must have an associated user account. Then they "build" their virtual store where they can sell their goods to other users(customers). A business can employee a certain number of virtual employees. All of these employees must setup a user account first.

So, the list of entities here is:

USER, FRIEND, ARTIST, HOUSE, HOUSE EVENT, EVENT PARTICIPANT, BUSINESS, EMPLOYEE, CUSTOMER.

Of course there are more entities, but I am trying to focus on the "people" entities and how to design that part only.

The person before me had one USER table with a user type code to designate regular user, artist, customer, adult, child. But there are many problems with that, not to mention violation of 1NF.

So I have a USER table which is basically the parent table of all people. Then I have tables like ARTIST, EVENT_PARTICIPANT, CUSTOMER, EMPLOYEE, FRIEND tables, all with a user_id fk pointing to the USER parent table. Is this making sense? Is there a better way?

2
0

one USER table with a user type code to designate regular user, artist, customer, adult, child

This is called a "Table-Per-Hierarchy" or TPH model, and it's quite common and definitely not a violation of 1NF. It simply has null columns.

The alternative "Table-Per-Type" or TPT model has its own problems. But those are the two main ways of modeling "inheritance hierarchies" in an RDBMS.

Neither is perfect as an RDBMS has no native notion of a "subtype". But Table-Per-Hierarchy is generally considered the simpler and better-performing option, as storing and retrieving wide rows with lots of nulls is typically cheaper than performing many joins.

2
  • Having a user_code column when a user can be multiple codes is not a violation of 1nf? Since a user can be both a creator, adult, artist, and/or a customer.
    – Jason
    2 days ago
  • This is not a pattern that supports "multiple inheritance". It's used to model single-inheritance hierarchies, which is what Java and .NET support. 2 days ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.