· 8 years ago · Feb 24, 2018, 02:00 PM
1user_email PK
2user_id PK
3password
4
5user_id
6friend_id_1
7friend_id_2
8friend_id_3
9friend_id_N
10
11Table Name: User
12Columns:
13 UserID PK
14 EmailAddress
15 Password
16 Gender
17 DOB
18 Location
19
20TableName: Friends
21Columns:
22 UserID PK FK
23 FriendID PK FK
24 (This table features a composite primary key made up of the two foreign
25 keys, both pointing back to the user table. One ID will point to the
26 logged in user, the other ID will point to the individual friend
27 of that user)
28
29Table User
30--------------
31UserID EmailAddress Password Gender DOB Location
32------------------------------------------------------
331 bob@bob.com bobbie M 1/1/2009 New York City
342 jon@jon.com jonathan M 2/2/2008 Los Angeles
353 joe@joe.com joseph M 1/2/2007 Pittsburgh
36
37Table Friends
38---------------
39UserID FriendID
40----------------
411 2
421 3
432 3
44
45CREATE TABLE IF NOT EXISTS `friends` (
46`id` int(11) NOT NULL,
47 `user_id` int(11) NOT NULL,
48 `friend_id` int(11) NOT NULL
49) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
50
51(
52 select friend_id
53 from friends
54 where user_id = 1
55 ) union (
56 select distinct ff.friend_id
57 from
58 friends f
59 join friends ff on ff.user_id = f.friend_id
60 where f.user_id = 1
61 )
62
63user_id -> users.user_id
64friend_id -> users.user_id
65friendVisibilityLevel
66
67user_id PK
68user_email
69password