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