MySQL Database Design Help?

Question

I am going through my database and what I want to do is re-design it because I feel like it's very messy and that I am doing it wrong. This is just for practice; I've never had anyone teach me so I would love to learn new ways and how to approach this.

What I have is a users table with the following fields:

id, admin, active, username, password, business_name, address, city, state, zip, phone, email, hours, hours2, credit_cards, logo_ext, package, theme, header_image, facebook_link, twitter_link, analytics, analytics_id, website_url, banner_image, member_forgot, home_page, about_page, contact_page

Wow, pretty messy right? Here are a few things I see:

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

I'm not too sure about the rest. Just to explain a few of the fields:

• Hours = Monday through Friday hours
• Hours2 = Saturday hours
• Credit_Cards = 1110 format (visa, mastercard, discover, amex)
• logo_ext = jpg, png - stores it when you upload logo
• package = 1, 2, or 3. Different level. Each level has its own template.
• theme = what stylesheet to use
• header_image = which image to use on the header for that account.
• analytics_id = I use piwik, so piwik stores a unique id that I need to store
• banner_image = 1-30 of different images there are to choose from
• pages = stores the text in the database, which they can change via wysiwyg editor.

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?

Any suggestions / help is appreciated. I love to learn new things and am very interested in making this database more efficient and flexible.

Thanks so much.

Answer 1

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, JOINs 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.