suppose we have two tables:
- Brands
- Users
i want to design a system where:
- Brands can follow Brands
- Brands can follow Users
- Users can follow Brands
- Users can follow Users
this is what i have done so far:
- ed_id: user_id of the followed
- er_id: user_id of the follower
note: the two tables are different but i haven’t written all the attributes to keep things simple
while this works fine among the Users and i can return the followers for each user with some simple queries, i don’t know how to establish a relationship between Users and Brands so that they can follow each other.
any help is appreciated!
Advertisement
Answer
There are a few ways to do this.
It seems that “user” and “brand” are very similar. They share many attributes – in fact, if you replace “brandname” and “username” with “name”, they’d be identical.
This could lead you to a range of different designs.
I you think “actually, there are lots of differences, I’ve just left them out to keep the question simple”, you might say that there is a superclass called User, and two subclasses called Brand and Person. There are several ways to model inheritance in relational databases. You might then have a model as follows:
Users ----- User_id (pk) hashed_password salt phonenumber address Person ------ person_id (pk) user_id (fk) person_name .... Brand ----- brand_id (pk) user_id (fk) brand_name .... Follow ----- user_id_follower (fk) user_id_followee (fk)
However, you might conclude that there is no meaningful semantic or behavioural difference between “person” and “brand” – that the type of user is merely an attribute of that user. In that case, life gets even easier:
Users
-----
User_id (pk)
hashed_password
salt
phonenumber
address
name
user_type ('brand', 'person')
Follow
-----
user_id_follower (fk)
user_id_followee (fk)