-1

How to create a Facebook database on small scale.

about the project:

  1. User can signup and create a account (info is store in UserTB table)
  2. User can edit there profile & address informaion (info is store in ProfileTB & addressTB tables)
  3. User can add their family member information. But Family member is Not a user of this application.
  4. Family Member can become a member of application but doesnt has to be

Issue: How can user add their family member information (sister/brother/dad)? User's family will also have first_name, address etc.. so maybe doesnt make sense to re-creating FamilyProfile or FamilyAddress Tables. Main issue is user can add their many Family Member but family member doesnt has to be a user of this application.

UserTB = track user login information
ProfileTB = Track user profile information
AddressTB = track user address information
FamilyTB =  user have multi family member linked to user table (Relationship is string, ex brother, sister, dad etc..).

I solove this issue by adding Linked_ID in each tables. but it has its own issues, for example. i could have 50 tables... and when family member becomes a user... than I will have to update user_ID in 50 different tables... Table and test data

click here to see ERD Image

Xin
  • 1
  • 2

1 Answers1

1

The AddressTB is unnecessary and must be merged with the ProfileTB. I know why you seperated Street into a different table (Normalization) but streets are just way too many and they can sometimes have multiple names (In some countries atleast), people can also have typos when entering their Street name and it's not worth it!

On the other hand you need the FamilyTB (I would rename this table to RelationTB for more clearance).The actual information for users are stored in ProfileTB and only their relations are stored in FamilyTB. (Read more here)

Also, Family_ID cannot be a primary key on its own! Because primary keys must be Unique; However, a user can have multiple brothers and that violates this rule. Change it the following so it's always unique:

Table = FamilyTB
Field = Family_ID (PK)
Field = User_ID (PK)
Field = Relationship
Keivan Ipchi Hagh
  • 190
  • 1
  • 10
  • Thanks. I am bit confused about `User_ID` beeing PK in `FamilyTB`. Shouldnt I link `User_ID` so that a User can have many Family Members. If I make `User_ID` PK, than wouldnt that make it one to one relationship? I will read the link you have me. maybe I missed something – Xin Nov 05 '21 at 14:14
  • do you thinking adding `Linked_id` a good idea? – Xin Nov 05 '21 at 15:09
  • @Xin, Both User_ID and Family_ID must be primary keys (think of it as a tuple), this way it's guaranteed to be unique. – Keivan Ipchi Hagh Nov 06 '21 at 06:09