[ale] MySQL help

[Alex Carver]

You're quite welcome, I'm glad you got something out of it.  As for
technical writing, I wrote too much in grad school and still have to
write too much in my current job to want to go into voluntary technical
writing professionally. ;)

Of course that changes if I ever attempt to secure a professorship at a
university somewhere in which case I'd be writing lesson plans and
textbooks.  But that's a bit further off and the writing is still not
completely voluntary but at least I'd have some advance warning. ;)

On 2015-03-20 15:44, David Jackson wrote:
> Hi Alex,
> 
> No doubt we'll get to Normal Form at some point, but I want to thank you
> for your most excellent explanation here.  Whatever text we read when we do
> get to it would most definitely benefit from your write-up!  Have you ever
> considered technical writing?  I would bet you've done a lot, because I
> know that explanations this clear don't just happen accidentally.  I wish
> my texts were as clear as your explanations!
> 
> Thanks again!
> Dave
> 
> On Sat, Mar 14, 2015 at 7:09 PM, Alex Carver <agcarver+ale at acarver.net>
> wrote:
> 
>> I know it was an assignment so you're limited but it was something to
>> keep in mind as the construction of that database is not the best
>> example of a proper design.
>>
>> The rule isn't for MySQL specifically, it's for database design in
>> general and is called Normal Form.  It applies to all relational
>> databases independent of the engines that support them (MySQL, Postgres,
>> etc.)  The concept isn't that the data is atomic more that it is not
>> repeated unnecessarily which maximizes storage (this is still a critical
>> thing for hypergiant databases used by NASA, Google, Amazon, etc.) and
>> also allows better query structure and operation.
>>
>> There are multiple levels of Normal Form starting with Zero, which has
>> no optimizations, going up to Five, which has the most (and sometimes
>> very complicated) optimizations (there are sometimes "extra" unofficial
>> levels referenced above Five but they dont' make much sense.)  Where you
>> stop all depends on how easy it is to visualize the database structure
>> at the higher NF levels (so that it can be implemented) and whether the
>> data is a natural fit.  In most cases you end up with a hybrid database
>> where most is at some NF level and small portions are at a lower level.
>>
>> The most common textbook examples for addressing NF start off with basic
>> address books/contacts lists.  You start with an address book with a
>> person's name and postal address:
>>
>> John Smith  123 Main St, Springville, IL 12345
>> Jane Doe    456 Side St, Springhills, IL 12345
>> Jim  Johns  789 Road St, Springvalley, IL 12345
>> Jill Dale   123 High Rd, Fallglen, OH 23456
>> Joe  Jims   456 Low Rd,  Fallcrest, OH 23456
>>
>> In a zero NF database, these would be recorded one at a time, lots of
>> repeated information (obviously the ZIP and state).  So it's obvious
>> that there is a minimization that can occur with the state at a minimum
>> by breaking it out into a table and then relating the address book table
>> with the state table.  Now when you store a state, you only have an
>> integer between 0 and 49 (assuming only the 50 states) and integer
>> storage is more efficient than a string of two characters in most
>> database engines.
>>
>> A less obvious one is the ZIP which actually could be broken into a
>> small set of foreign lookup tables.  The number of ZIPs is limited and
>> only certain ZIPs exist in certain states because the US is divided into
>> 10 regions.  So you could have at least one table that matched a ZIP
>> with a state and use that in the address book instead.  Then you store
>> the ZIP only once, the state is already matched to the ZIP and thus the
>> state is only stored once.  The address book then only needs to point to
>> the ZIP table and the ZIP table maps to the state so you can recreate
>> the state information without having to have a "state" column in the
>> address book.
>>
>> By example:
>> ZIP_State_Table:
>> ID  ZIP      State
>> 1   12345    1
>> 2   12346    1
>> 3   23456    2
>> 4   23457    2
>>
>> State_Table:
>> ID       State
>> 1        IL
>> 2        OH
>>
>> Now the address book only needs:
>> Name, Street Address, City, ZIP_ID
>>
>> Note that the state field is not required.  You get the state by
>> chaining the three tables together.  And the ZIP doesn't have to be
>> explicitly stored, just the ID (so that other postal codes could be used
>> later such as Canadian).  You could extend this to a table of Cities
>> since ZIPs encode onto cities and that would eliminate the need to store
>> the city in the address book.  This is getting closer to Four and Five
>> NF since you're abstracting a lot of data and really paring down the
>> original table.  The reality is that most places like Amazon do exactly
>> this.  You don't need to store everyone's city over and over (and waste
>> the space doing so) because you know every city.  Just record them once
>> and point to them.
>>
>> The other part of NF is where pointers should go.  This is less an
>> atomicity of data and more of a logical structuring.  For your database,
>> all animals need food so the animal table should really carry the food.
>>  The food table itself is only the NF conversion to minimize data
>> replication.  Since foods can be eaten by more than one animal, the
>> current arrangement of your example tables (with animal ID in the food
>> table) causes unnecessary replication of data (a food gets repeated)
>> which goes against NF rules.  The map table I suggested goes up another
>> level of NF because it avoids replication of data in the animal table,
>> too, since one animal can eat more than one food.  But, of the two
>> evils, replicating the animal was the lesser evil because it's more
>> logical in a database sense compared to replicating food mainly because
>> the food is more likely to be replicated with greater frequency than the
>> animals (any individual animal might eat less than five food items but
>> any food item can potentially be eaten by an entire zoo's population so
>> you have many more potential repetitions of the food than the animal).[1]
>>
>> In the end there's no real limit to how far you should go in terms of NF
>> other than whether it makes implementing the database much harder than
>> needed for the task.  For Amazon, high level NF is crucial otherwise
>> they'll burn through disk space in a hurry.  For a small DB, maybe not
>> critical at all.  My personal address book might only be 50 entries so
>> do I need to spend the time doing it?  Not really, there's no savings
>> for me except that it's just good practice.
>>
>> NF is not hard and fast but it's very useful for making databases work
>> better.  It also allows you to eventually slice and dice your data in
>> ways that might otherwise be very difficult to achieve with flat or low
>> NF databases.  It's harder to create an example of this since easy to
>> type and follow database structures don't lend themselves to difficult
>> analysis (by design).  But big research databases are a prime place
>> where this happens.  A researcher may piece together data in various
>> ways to extract trends but this would be difficult to do if the database
>> was a monolithic table or otherwise used a very low order NF that didn't
>> break out all the data as much as possible.
>>
>>
>> [1] Another way to visualize this is to switch the animals to zoo
>> visitors (humans) and the food that the visitors eat at the zoo (which
>> is usually a limited set of park fare like hot dogs, hamburgers, chicken
>> wings, soda, etc.).  If you had to track every visitor and what they ate
>> for lunch, are you more likely to put the pointer to food in the visitor
>> table or a pointer to the visitor in the food table?  If you put the
>> food pointer in the visitor table, then each visitor might have two or
>> three records (one visitor bought a soda, a hot dog, and a soft pretzel
>> so three records for John Doe).  The food table here would still only
>> have the few items you sell  at the zoo (hot dog, hamburger, chicken,
>> soda, water, pretzel, ice cream.)
>>
>> But how many of those visitors at a busy zoo bought a hot dog?  If the
>> pointer is reversed and your food table points at a visitor then your
>> food table might suddenly have 10,000 hot dog entries.
>>
>> If your zoo gets 100,000 people a day (big zoo) and every person buys
>> three food items on average then your day's table of visitors will be
>> 300,000 records and the food table has just 7 using the first
>> arrangement (visitor points to food).  If you reverse the pointer (food
>> points to visitor), your visitor table is 100,000 records (every
>> visitor) and each item in your food table might have been purchased by a
>> third of the visitors for a total of over 230,000 records.
>>
>> The final tally, visitor points to food is 300,007 records and food
>> points to visitor is 330,000 records.  Net savings of 30,000 records.
>>
>>
>> On 2015-03-14 14:34, David Jackson wrote:
>>> Thanks Alex!  Wow.  Very impressive analysis!
>>>
>>> This has been for an assignment, so I couldn't change the tables, but
>> your
>>> way of gluing the info together makes sense to me, so thanks!  I think
>>> they're just trying to keep it simple for the class
>>>
>>> It looks like for MySQL you want to "atomize" the data as much as
>> possible
>>> to keep the structure out of the way of possible relations among the
>>> data??  I guess it's a judgment call to not get too "atomic" though, huh?
>>>
>>> Dave
>>>
>>>
>>> On Fri, Mar 13, 2015 at 5:55 PM, Alex Carver <agcarver+ale at acarver.net>
>>> wrote:
>>>
>>>> There are four JOINs available to you, LEFT, RIGHT, INNER (this one is
>>>> usually just the unadorned JOIN keyword) and OUTER.
>>>>
>>>> To see if you have an animal with no food and given that your food table
>>>> is on the right of the animal table in the query you offered you need to
>>>> use a LEFT JOIN.  The LEFT JOIN ensures that the left table takes
>>>> precedence during row generation such that missing matches result in
>>>> NULLs in the fields from the right-side tables:
>>>>
>>>> So change your query to:
>>>>
>>>> SELECT id, name, family, feed FROM animal LEFT JOIN food ON animal.id =
>>>> food.animalid;
>>>>
>>>> That gives you everything.  Then add a WHERE clause to filter out
>>>> animals missing food:
>>>>
>>>> SELECT id, name, family, feed FROM animal LEFT JOIN food ON animal.id =
>>>> food.animalid WHERE feed IS NULL;
>>>>
>>>> The unadorned JOIN (which is a INNER or STRAIGHT JOIN) will only show
>>>> records where there are matches in all joined tables.  The RIGHT JOIN
>>>> variant will reverse the precedence of the tables (LEFT and RIGHT JOINs
>>>> work the same, the precedence is reversed and it's mainly used when you
>>>> join many tables so that you get the precedence correct for the needed
>>>> query).
>>>>
>>>> On an aside, the table as constructed isn't quite proper according to
>>>> the rules of Normal Form (how databases are laid out).  Your food table
>>>> should not have an animal ID.  Instead your animal should have a food ID
>>>> if it can only eat one type of food.  Alternatively there should be a
>>>> third table which maps animal ID to food ID that way an animal can eat
>>>> more than one food.
>>>>
>>>> Two table variant:
>>>> Animal: {id, name, family, weight, foodid}
>>>> Food: {id, feed}
>>>>
>>>> Three table variant:
>>>> Animal: {id, name, family, weight}
>>>> Food: {id, feed}
>>>> AnimalFoodMap: {animalid, foodid}
>>>>
>>>> In the three table variant you join the animal and food tables using the
>>>> AnimalFoodMap as "glue":
>>>>
>>>> SELECT name, family, weight, feed FROM Animal LEFT JOIN AnimalFoodMap ON
>>>> animal.id = AnimalFoodMap.animalid LEFT JOIN Food ON
>>>> AnimalFoodMap.foodid = Food.id;
>>>>
>>>> If an animal has more than one food type, the animal is listed once in
>>>> the Animal table, the foods are each listed only once in the Food table
>>>> and the AnimalFoodMap table contains the appropriate pairings.  Your
>>>> example has more than one food per animal and also one repeated food so
>>>> let's modify it:
>>>>
>>>> Animal (table):
>>>> id  name    family   weight
>>>> 1  Seymore  Snake    10
>>>> 2  Gerard   Giraffe   120
>>>>
>>>> Food (table):
>>>> id  feed
>>>> 1   mice
>>>> 2   leaves
>>>> 3   grass
>>>>
>>>> Now the magic AnimalMapTable:
>>>> animalid   foodid
>>>> 1          1
>>>> 1          2
>>>> 2          2
>>>> 2          3
>>>>
>>>> (Note that I'm skipping over some things such as keys that should be
>>>> used to ensure there are no duplicates)
>>>>
>>>> After doing this there are several additional ways to write up queries
>>>> to make an output similar to what you have (single line for an animal
>>>> plus all the entries for the foods) which requires some more complicated
>>>> query structure.  But for now, the query I wrote above would output:
>>>>
>>>> id name      family  feed
>>>> 1  Seymore   Snake   mice
>>>> 1  Seymore   Snake   leaves
>>>> 2  Gerard    Giraffe leaves
>>>> 2  Gerard    Giraffe grass
>>>>
>>>>
>>>> On 2015-03-13 10:52, David S Jackson wrote:
>>>>> Hi,
>>>>>
>>>>> I'm a MySQL newbie.  I'm just starting to use Python to talk to MySQL
>>>>> databases, but first I need to understand the MySQL query language!
>>>>>
>>>>>
>>>>> So, I have two tables:  (zoo) animal and food.
>>>>>
>>>>> animal has the columns ID, NAME, FAMILY, WEIGHT
>>>>>
>>>>> food has  ID, ANIMALID, FEED
>>>>>
>>>>>
>>>>> So if I go: select id, name, family, feed from animal JOIN food ON
>>>>> animal.id=food.animalid;
>>>>>
>>>>>
>>>>> I get something like:
>>>>>
>>>>> ID   NAME        FAMILY      FEED
>>>>> 1    Seymore   Snake        mice, leaves
>>>>> 2    Gerard      Giraffe       leaves, grass
>>>>> ...
>>>>> etc
>>>>>
>>>>> So, if I have a situation where I want to see whether an animal has
>> been
>>>>> added the animal table but may have not been added to the food table,
>>>>> how would I compose that query?
>>>>>
>>>>> That is, the animal could have been added to the animal table and could
>>>>> get an id, say 10, but the food table could have 20 foods already
>>>>> entered and the animal-id would be used several times.
>>>>>
>>>>> I was thinking, is there a way I can ask, "does the highest animal.id
>>>>> number equal the highest food.animalid number"?  If not, what animal is
>>>>> not getting fed?