Foreign Keys for Shopping List? SQL and Python

mikeyv 0
Viewed 40

I am working on a fairly large project while I learn Python and SQL. I've actually built the whole thing across 2-3 google sheets, but it runs slow, has issues, and is not user friendly at all.

I thought it would be fun to try and build it with Python as I learn to code.

There are a bunch of future plans, which is why there are is so much info in my Ingredients Table...Just wanted to note that as I know all the fields aren't relevant for my question below.

I have two tables: 

**Ingredients Table**
id - PK
food_desc
protein
carbs
fat
price
aisle (foreign key to below)


**Aisle Table**
id - PK
aislelabel

The Aisle Table uses an id and a label because I want to have the ability to sort it by the ID in a certain order and not all the aisles for the Ingredients are numerical. For example, I'll have: 

ID 1 = Produce
ID 2 = Seafood
ID 3 = 1
ID 4 = 2
ID 5 = 3
....
ID 30 = Dairy

This way, when I return a list I can sort to have Produce and Seafood before Aisle 1,2,3..etc...and have the Dairy Aisle at the end of the list (Also the order I shop in when I enter the store!).

I don't know how to make this happen. How do I bring in the aisle id to sort, but only return the aisle label when referencing the ingredient ID?

mysql sql python
1 Answers

A FOREIGH KEY gives you two things. Neither are "required":

  • An index -- for performance. An equivalent INDEX could be added by other means.
  • A consistency check -- for catching dangling references. That's a "nice to have".

FKs may be added to just about any database with more than one table.

Rick James0
Related