October 6, 2021 at 6:42 pm
Hello,
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 a 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?
Microsoft Certified Professional: MCSE Data Management and Analytics
October 7, 2021 at 7:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
October 7, 2021 at 7:59 pm
It seems to model the real world. The additional complexity appears to be necessary.
You hit on one of the big shortcomings of the old approach -- a single user type code ignores the fact that a person can have multiple roles/relationships (entities) with respect to another -- e.g., a person can be both an employee and a customer of a business.
I would view the Friend, Event_Participant, Employee, and Customer as role/association tables -- they associate/relate one person to another person or organization.
Artist seems different -- in that it is a role/identity of a person with inherent attributes independent of relationships with others -- you can be an artist without any customers or patrons -- perhaps you create public works of art. Associations to artist should perhaps be defined in something like Artist_Customer, Artist_Patron, or Artist_Subscriber tables.
Business also seem to be an entity unto itself w/ attributes & relationships. Based on your comments, it will probably have something like either owner_user_ids or perhaps multiple roles besides Employee. I'm not sure what House represents.
Business (and perhaps House) represents organizations (could be others too). Depending on what House is, you might want an Organization table that kinds of corresponds to the user table, and then have subtypes of organizations such as Business for attributes & relationships unique to that subtype.
Event is another distinct entity-- it may have a physical location (business or house),or may be virtual (you may want an entity to represent persistent virtual sites). It may have other relationships besides Event_Participant & location.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply