Solved

Need help determining how to properly setup MySQL database

Posted on 2014-09-04
9
434 Views
Last Modified: 2014-09-08
I have built a website on my Linux host and i'm using MySQL to display and control data. I have a base set of results that gets sent to MySQL server from users devices. This is what that table looks like.

CREATE TABLE appfilter.userapps (
  name varchar(50) DEFAULT NULL,
  activity varchar(120) NOT NULL,
  class varchar(120) NOT NULL,
  device varchar(50) DEFAULT NULL,
  version varchar(50) DEFAULT NULL,
  theme varchar(50) DEFAULT NULL,
  date varchar(50) NOT NULL
)
ENGINE = INNODB
AVG_ROW_LENGTH = 149
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

Open in new window


This table cannot change. I have many developers using my base source code and is on tens of thousands devices. I could change it but it would take a while to get back out to users.

So that data builds a results table that supplies info on how popular apps are.
These are the tables that I currently have to control that.

1.png
Now this website i have created takes the users current data and compares to my data received from users devices.

Here is the table that is created for users to upload their data to

2.png

So, here is where I am at. I know that my data is not normalized at all. My results are pretty quick on site but I know if i setup everything proper I could be doing much better. I have tried to research on my own and I cannot figure out how to get everything in tables and where to index, add foreign keys, etc....

Some background on the data im manipulating.


The user table
user - current user uploading data to site
category - category the app falls into (this will vary from user to user)
name - name of the app
activity full - concatenation of activity + / + class
activity - unique identifier to the app (some apps can have duplicates only if the old one is not working anymore)
class - the main class file for the activity. ONE activity can have MANY classes.

The results table
activity - same as above
name - name of the app
count - number of times the app was send to my server from users device
countlast - last count before i run update to see how big app has grown

The results_class table
activity - same as above
class - same as above

the results device, theme and version are used to determine stats about how the data in results is built

Main comparisons I do on site are as follows
- compare user uploaded activity to results activity to see what they are not including. Its sorted by count so they can see the most popular first.
- Add classes to users data. So if they have an activity name but not ALL classes i add them.
0
Comment
Question by:bignadad
  • 5
  • 3
9 Comments
 
LVL 19

Expert Comment

by:Iammontoya
ID: 40304459
Here's the quick and dirty version:

normalizing is about keeping data unique across a table so that items are not repeated (again, simple version).

So you normally have things like this:

tblUser
   userID
   firstName
   lastName
   address
   address2
   city
   st
   zip
   phone (more on this later)


tblApp
    appID
    name
    appCount (maybe)

tblActivity
    activityID
    name
    classID

tblClasses
   classID
   name


etc.. etc.. I'd have to know a lot more to build it... I mentioned phone, because if we 'Really' follow normalizing rules, then you could argue that phone number would have it's own table, because a person in these days, could have multiple phone numbers. Same with email addresses. So I could have a table called tblPhoneNums that has:

phoneID
UserID
number
category (cel, home, work1, work2, etc... )

And so forth.. if you do a search you will find Database design for Mere Mortals. An absolutely excellent book. I've seen it out there as a free download, but I won't post the link because I don't know if the author is legitimately offering it for free. Nonetheless, well worth the investment.
0
 
LVL 19

Expert Comment

by:Iammontoya
ID: 40304465
your keys are how one table connects to another.

tblUsers
userID     <--------- Primary Key


tblPhoneNums
phoneID      <---------- Primary Key
userID        <------------ foreign Key   (here were are pulling from a foreign table to identify which user owns this phone number.
0
 
LVL 2

Author Comment

by:bignadad
ID: 40304629
okay. great info. something like what you posted with table layouts is what im looking for. Think i can do the rest if i can just visualize the structure. Everything i look at online doesn't seem to relate.

So following up on what you posted. Coulple of questions

- How will tblApp and tblActivity relate?
- In my user upload table do i just put a foreign key for appID to bind it?
- Also the will any of these id's be auto increment?

tblApp
    appID
    name
    appCount
    appCountLast

tblActivity
    activityID
    name
    classID

tblClasses
   classID
   name
0
 
LVL 19

Expert Comment

by:Iammontoya
ID: 40304892
I am posting from my car, so please forgive me for the brevity. Yes you normally put the IDs when you referred to them and another table.also, most of the time the IDs are auto increment. That way they always have a unique ID that increases by one every time you a new record.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 19

Expert Comment

by:Iammontoya
ID: 40304894
As for the activity, you normally ask yourself a series of questions. Who is the user? what is the date?  which is the app? Those questions will lead to connections to your activity table. Every time there is a related key in a different table you add the ID to that activity table. So your activity table may have a user ID, and app ID, and category ID, etc.
0
 
LVL 2

Author Comment

by:bignadad
ID: 40305048
Again, thank you for all your help. Im starting by determining what tables i need then ill accept your advice as solution and create another question if i have question on id's and such.

Here is what i have

tbl_app
app_name
app_activity

tbl_class
app_class

tbl_stats
app_count
app_count_last

tbl_user
user_name
user_app_category
user_app_icon

tbl_user_app
user_app_name
user_activity
user_class

tbl_device
device_name
device_id
device_count

Open in new window


How does this look?

on tbl_user_app, should the class be broken out there as it in in app table?

trying to wrap my head around this but i think when the user uploads their data it would update the app table with any entries not already there, THEN just use id references to the main table instead of actual activity/class names. Am I thinking of this correctly?
So it would be

tbl_user_app
user_app_name (name would be unique to each user)
user_activity_id
user_class_id
0
 
LVL 2

Author Comment

by:bignadad
ID: 40305105
So this is what i have come up with. Am I even close?

Untitled-picture.png
0
 
LVL 19

Accepted Solution

by:
Iammontoya earned 400 total points
ID: 40305633
Youre on the right track.

For example:

tbl_user would normally have some more info (normally)
I would need to understand more of what the tbl_user_app is supposed to do, and I don't understand why you have activity_id on the tbl_user.

In my experience, there's usually some sort of main function that drives the whole thing. From what you have described, activity would normally do that. Let's take a different example: If we build a dog kennel app we will need some basic questions:

What do I need to track?
What are my daily activities?
What other info is useful?

To that question you can have answers like:

I need to know which dog is where.
I need to keep track of what they eat
I need to keep track of vaccinations
I need to contact the owner
etc...

I can start with my daily activity: Someone comes in the door and...

date:
owner:
dog's name:
food:
allergies?
medication?
vet name
assigned to kennel no.
.. and so on

from here I can see that I need some tables.

owner_tbl
owner_id
fullname
phone no

I would NOT put the dog's name here at all, because the owner could have multiple dogs

dog_tbl --- all my info about the dog
dog_id
owner_id  --who owns the dog
dogname
allergies (you could build an allergies tbl)
vaccinations:

vet_tbl

vet_id
vetname
phone no
address
address2
city
st
zip

kennel_tbl
kennelid
kennelsize

and for this brief example, let's build our activity table

activity_tbl
date
owner_id
dog_id
kennel_id

so.. all this to say, the activity table is pretty much driving everything in this example. Notice that I don't have an activity_id in the user_tbl.  I have a user_id in the activity_tbl because activity will "pull" data from the user_tbl.

One owner can come in multiple times.
One owner may have multiple dogs.
etc... those are the infamous one-to-many relationships

I mentioned an allergies table.... one dog could have multiple allergies.
One vet could be the vet for multiple dogs..and so on.

I hope this helps.
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
ID: 40308477
Just a couple of general comments.  There are three important numbers in computer science.  They are zero, one and infinity.  Let your objects be defined by the interface between them.   A concrete example might be people and telephones.  A one-to-one or one-to-none relationship would have been sensible in the 1960's when all phones were wired to the wall, before the proliferation of car phones, then portable phones, then Blackberries, etc.  Today most people have multiple phones - office, home, mobile.  And in some circumstances (think ship-to-shore) a single phone serves several people, perhaps a rotating group, as in a charter vessel.  So the old one-to-one relationship does not work any more, and a many-to-many relationship has evolved.  Your tables should reflect this "new" reality.  A phone object no longer contains a person object and vice-versa; these are two different objects with an interface between them.

A perfectly normalized data set is not likely to be as efficient as a somewhat denormalized data set, when data volumes explode.  If you build anything that becomes popular, you will find yourself having to scale up very, very fast.  At scale, to run your queries fast enough, you will find yourself denormalizing the data and just throwing hardware at the problem.

Some interesting viewpoints on different sides of the question can be found if you make a Google search for the exact phrase, "Should I Normalize My Database" -- it's worth reading the ideas from several experts.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now