Behavior, Content, Money – 3 Things you should never give away for free!!!

BCmoney MobileTV

Using MySQL to JOIN data in two tables, where part of the data is NULL

Posted by bcmoney on January 25, 2009 in SQL with No Comments


No Gravatar
InnoDB_int

InnoDB_int (Photo credit: Fenng(dbanotes))

MySQL can be incredibly useful and simplistic by times, while other times its lack of full Joining (i.e. LEFT OUTER JOIN gets evaluated the same as LEFT JOIN), and Referrential Integrity can become frustrating when you are trying to provide these functionalities within the Database Server.

 

(I know, I know InnoDB solves most of these problems, but still not complete)

So the problem is this:

Table 1 – ACCOUNT
account_ID account_username

Table 2 – ACCOUNT TYPE
account_type_ID account_name start_url end_url

 

Sparing too many details, I wanted to store a list of a user’s accounts on other popular websites all over the web, while still keeping a normalized database (could get out of hand otherwise, if you had a large number of users and crammed both accounts and account types into one table). Thus the solution is to of course normalize to two tables, one for the various users’ account names and one for the account types (i.e. Amazon, eBay, YouTube, Facebook, MySpace, etc…)

 

This brings about one little problem in that you’ll need to perform a JOIN to present each separate user with “their accounts” plus all the other available accounts that they have yet to create/add to your site for merging. Should be a basic JOIN, no problem right?

Wrong… good luck trying to solve that smoothly without multiple different forms, one for accounts they have added, one for accounts they haven’t added, and one for accounts that they can still possibly add. This can (and should) be reduced down to 2 screen, one for managing all their accounts, and one for displaying all the accounts they have added thus far. A far better solution… but in order to do that we need to JOIN on existing and non-existing data.

Read on for the solution…

Read the rest of this entry »

BC$ = Behavior, Content, Money

The goal of the BC$ project is to raise awareness and make changes with respect to the three pillars of information freedom - Behavior (pursuit of interests and passions), Content (sharing/exchanging ideas in various formats), Money (fairness and accessibility) - bringing to light the fact that:

1. We regularly hand over our browser histories, search histories and daily online activities to companies that want our money, or, to benefit from our use of their services with lucrative ad deals or sales of personal information.

2. We create and/or consume interesting content on their services, but we aren't adequately rewarded for our creative efforts or loyalty.

3. We pay money to be connected online (and possibly also over mobile), yet we lose both time and money by allowing companies to market to us with unsolicited advertisements, irrelevant product offers and unfairly structured service pricing plans.

  • Archives