Beginning Django E Commerce
398 pág.

Beginning Django E Commerce


DisciplinaProgramação I24.583 materiais280.005 seguidores
Pré-visualização50 páginas
'Ruby Axe Guitar'; 
DELETE FROM product_table WHERE name = 'Ruby Axe Guitar'; 
The latter two commands are generally performed with the WHERE clause. If you have 10,000 
products in your product table, and run the DELETE command without a WHERE, you will delete all 10,000 
products! Similarly, if you run UPDATE without a proper WHERE clause, every single product will be 
updated with the change you intended to make to just one. These are pretty easy mistakes for a 
developer to make, particularly at the end of a long workday. My advice is that when you\u2019re working 
directly with SQL at the command line or within the shell, you should type your WHERE clause first. 
SELECT can also take a WHERE clause, so you could try running a SELECT with your WHERE clause to test 
that it really does only retrieve the rows you want to UPDATE or DELETE.2 
 
2 If nothing else, this mistake is one of the many reasons that you should be performing regular backups on your 
application\u2019s database! 
www.it-ebooks.info
CHAPTER 3 \u25a0 MODELS FOR SALE 
 
42 
 
Lastly, regarding raw SQL queries, we should discuss the inclusion of an ORDER BY clause in your 
SELECT queries. If you retrieve more than one record, you have the option of specifying an ORDER BY 
clause, which lets you explicitly determine how your results will be sorted. As a simple example, the 
following query will return the product records the same as before, but they will be ordered, 
alphabetically, by the name field: 
SELECT name, description, price, category \ufffd 
FROM product_table ORDER BY name 
ORDERING AND CASE SENSITIVITY IN DATABASE ENGINES 
When you sort results by an integer column, the sorting behavior will make sense. When sorting by a field 
containing character data, the exact ordering of query results when you use an ORDER BY clause depends 
on the way that your database engine orders different strings lexically. By default, MySQL sorting is case-
insensitive, while PostgreSQL is very case-sensitive. How the results are sorted is affected by the collation 
setting of your database engine. If the field is able to contain NULL values\u2014that is, the absence of any 
value at all\u2014then you may not get the exact ordering you were hoping for. 
For more on the nuances of ORDER BY clauses, consult the documentation for your own database server. 
What Makes a Relational Database 
Of course, any real site out in the wild is going to have more than one table in which it will store its data. 
Your e-commerce site is going to be storing customer information, orders, and shopping carts as well as 
product data. As it happens, these tables almost never stand alone; they are linked to one another in a 
logical way through relationships between their records. 
While you might get away with coding an entire Django project without writing a single line of SQL, 
you\u2019ll be hard-pressed to architect anything without understanding these relationships between models. 
They aren\u2019t terribly complicated: there are three types of these relationships: one-to-many, many-to-
many, and one-to-one. These describe relationships between the specific records in your tables. Let\u2019s 
look at these individually in turn. 
One-to-many: Occurs when a single record in one table can be linked to one 
or more records in another table. As one example, take products and their 
corresponding product reviews. Products can potentially have one or more 
reviews, but reviews typically belong to only one single product. This a very 
common relationship that you\u2019ll be using extensively throughout this book, 
so it\u2019s key that you take a moment to understand how it works and let the 
concept sink in. 
Many-to-many: This relationship can be found when a single record in one 
table can be linked to one or more records in another table, and a single record 
in the other table can be linked to one or more records in the first table. We\u2019ll 
see a perfect example of this coming up shortly in this chapter, between 
categories and their products. A category can, of course, have more than one 
product. There will be more than one guitar that we\u2019re listing under \u201cElectric 
Guitars.\u201d However, the converse can also be true: a product may fall into more 
than one category. You may create a category called \u201cHammers of the Gods\u201d for 
www.it-ebooks.info
CHAPTER 3 \u25a0 MODELS FOR SALE 
 
43 
 
those top-of-the-line Gibsons and Flying Vs, and any one guitar may fall under 
this category, as well as \u201cElectric Guitars.\u201d 
Relational databases like MySQL do not support many-to-many relationships 
between two tables. The solution is to create a third table that acts as a join 
between the two tables, and holds records that represent relationships between 
them. We\u2019ll come back to that when we set up our tables. 
One-to-one: This is when one record in a table can be linked to exactly one 
record in another table. In all likelihood, you\u2019ll rarely ever use this type of 
relationship in practice, simply because if you have a one-to-one relationship 
between tables, it will usually make more sense to combine the data from the 
two tables into a single table. 
So why all the linking? The most important reason to spread data out across multiple tables is to 
eliminate redundancy. This makes things terribly convenient for you. Ideally, whenever you add, edit, or 
remove data, you\u2019d like to do it in just one place. 
Let\u2019s look at how this could help you. Here is a listing of some dummy product data that we were 
talking about doing in Excel earlier: 
 
Name Description Price Category 
Ruby Axe Guitar This is a great guitar! 249.99 Electric Guitars 
Erlang Metronome Good metronome! 39.99 Accessories 
Haskell Drum Set Bang on these! 599.99 Drumsets 
 
Notice that we\u2019ve got the category name stored in this data. That\u2019s okay for now, because we only have 
one product in each category. But what if we start adding electric guitars? We\u2019re going to be putting in the 
text \u201cElectric Guitars\u201d quite often, once for each product. Also, if we ever decide to change the name of the 
category from \u201cElectric Guitars\u201d to \u201cKiller Axes,\u201d we\u2019re going to have to update the category name for every 
single product in this category. Furthermore, there is not a way to map a product to more than one 
category, unless you start storing duplicate records of the product data. Clearly, this will quickly turn into a 
maintenance nightmare where database angels fear to tread, and not a viable solution for us. 
The best thing for us to do is create two tables, one for category data, and one for product data, and 
link them together. In the case of a simple one-to-many join between the tables, we would place an 
integer field called category_id in the product table that references the id field in the category table. 
When the primary key of a table appears as a reference to that table in another table, that field is referred 
to as a foreign key. 
This process of spreading data out across multiple, linked tables in order to reduce duplicate data is 
called normalizing your database. The most common methods of normalizing data in tables are 
numbered, starting with the non-first normal form (NF2), first normal form (1NF), and so forth, up to 
sixth normal form (6NF). Despite the range of choices, there are data redundancy issues with 2NF and 
below, and complexities that arise with 4NF and above, which make them poor choices given the 
requirements of our project. 
The third normal form (3NF) is the level of data normalization that database administrators tend to 
shoot for when designing their databases. At this level, any attributes on the model that don\u2019t describe 
something about the model itself are normalized into a different table. In the list of products in the 
preceding table, where the category is listed with each product, the