admin and active is just a bunch of 0's and 1's so can I do something
like this? Since there will be 99% active and only a couple that will
be inactive.
admin - id, user_id
inactive - id, user_id
social - id, user_id
facebook_link, twitter_link
member_forgot - id, user_id, forgot_code
Actually no, I wouldn't do this and keep it in the same table. The flag should be enough to distinguish between these two. The only difference in your queries is a active = true
. Personally, I wouldn't consider this a bad design choice (altough your idea is not wrong, I would not use it in this case with - I'm guessing - a small set of data). Since only on set of data belongs to a single user, JOIN
s would make not that much sense.
I really want to practice splitting it up in different tables even if
it is not a huge database, or is that a bad idea? So if I delete a
user from the users table, it will delete all of the other records in
the other tables associated with that user_id?
Again, I wouldn't do that personally(!). I don't see a benefit of seperating user centric data from the user. If you fear losing the data on deletion, introduce a deleted
flag. This way, you will never lose data and your application just respects the deleted
flag in your queries (this would also apply if you split the data accross tables).
Credit_Cards = 1110 format (visa, mastercard, discover, amex)
Storing credit cards is always very sensitive, according to PCI (as far as i remember) you are not allowed to store the CVV code (hope someone can correct me on that). But since this seems to be a fictional application, that should not be a problem.
Ok, first off I think you are almost there. From looking at the schema and data one thing I have noticed is that you seem to have overlooked the fact that a friendship is bi-directional. So when you create a friend entry from a request you also need to create one in the other direction as well:
INSERT INTO `default_friend` (`friend_id`, `user_id`, `is_suscriber`, `privacy`, `created_at`, `friend_list_id`, `approved`)
VALUES (1, 2, 1, 0, '2012-08-13 18:16:11', 0, 1);
After you have done that your query should be more like the result you are after. Running this query:
select distinct u.id as `user_id`, u.username, f.id as `friend_id`, f.username as friend, s.*
from default_users as u
left join default_friend as df on df.user_id = u.id
left join default_users as f on f.id = df.friend_id
left join default_status as s on s.user_id = u.id
left join default_comment as c on c.status_id = s.status_id
order by s.status_id;
returns the following result set:
user_id username friend_id friend status_id message created_at privacy user_id is_reply device
1 admin 2 demo 1 dasdasdasdasdasd 2012-08-13 19:45:37 NULL 1 0
2 demo 1 admin 2 dasdasdasdasdasd 2012-08-13 19:46:03 NULL 2 0
1 admin 2 demo 3 dasdsad344hbvnbnhjhgjhjghjhj 2012-08-13 21:54:53 NULL 1 0
Is this anywhere near what you are looking for?
Best Answer
That's what
GROUP BY
is used for. Get one row (per group). In this case, it will show all distinctuser_id
values and for the rest of the columns, you can (have to) use aggregate functions likeMIN()
,MAX()
,AVG()
,SUM()
as you will have more than one values per group and only one can be shown.MySQL allows also the following unorthodox solution, that will return one (more or less random) comment per user:
This last query will not work but raise an error if the (stricter)
ONLY_FULL_GROUP_BY
mode is enabled. In the recently released 5.7 version, this mode is the default and a new function,ANY_VALUE()
, is provided. For more details, see the MySQL Handling ofGROUP BY
page. The query can be written now:Note that with either the "unorthodox" version or using the recent
ANY_VALUE()
function, if we add more columns in theSELECT
list, their values is not guaranteed to be from the same row, just from a row in the same group. The way they are selected is not exactly random, depends on the execution plan and the indexes used.