Users and Bands

The core of the system will be the Users, the Bands, and the relationship between the two. As such, one of the first steps is determining the basic profile elements for both, and how to link them.

(* = required field)

Users

• First Name*
• Last Name*
• E-mail Address (used as login and primary key)*
• Password*
• Biography/About Me
• Date of Birth
• Gender
• Profile Image
• City/State*
• ZIP
• Personal website URL
• Instruments Played

Bands

• Band Name*
• Unique Identifier (used for the site url and primary key)*
• Formation date
• Band Bio
• Main Band Image
• Contact Email
• Contact Phone
• Band site URL

Band Members

• User key (e-mail)
• Band key (url identifier)
• User role (determines whether the user can administrate or only edit band profile)
• Instrument(s) played in band

I'm thinking that the User key field will also be permitted to be just a name, if the member is not a user in the system. Then the query for data will determine whether to display the name in the Members table or the name in the Users table. For example, if we start with these three stripped down tables:

mysql> SELECT * FROM Users;
+-------------------+-----------+----------+
| email             | firstName | lastName |
+-------------------+-----------+----------+
| izenman@gmail.com | Joe       | Izenman  |
+-------------------+-----------+----------+

mysql> SELECT * FROM Bands;
+---------------------+------------------+
| name                | ident            |
+---------------------+------------------+
| This Shirt Is Pants | thisshirtispants |
+---------------------+------------------+

mysql> SELECT * FROM Members;
+------------------+-------------------+
| band             | member            |
+------------------+-------------------+
| thisshirtispants | izenman@gmail.com |
| thisshirtispants | Jake Westhoff     |
+------------------+-------------------+

we can do the following:

mysql> SELECT IF( U.email IS NOT NULL,  
          CONCAT(U.firstName, ' ', U.lastName), 
          M.member ) AS name 
       FROM Members M 
          LEFT JOIN Users U ON U.email = M.member 
       WHERE M.band = 'thisshirtispants';
+---------------+
| name          |
+---------------+
| Joe Izenman   |
| Jake Westhoff |
+---------------+

This will allow seamless integration of users and non-users into member lists. At member addition time, the band administrator will be prompted to either enter a member name or search the user archive for that member's account reference.