How do you make codes/lookup tables in Elixir?

Question

I am looking for a/the best way to make lookup tables or use codes in a relational database made from Python's Elixir. I am not even sure my terminology here is correct.

For example, I have a Location table that has a Region column. I want the values for the Region column to only have values of "North America", "Central America", "South America", "Asia/Pacific Islands", and several others. The list of values might change in the future.

How can this be done with Elixir? Using an Enum seems like a bad idea because the values are long text strings. It seems that some kind of code would be better (like 1=North America, 2=South America, etc.) How do I store and reference these codes in the database?

Answer 1

One suggestion would be to normalize your data, i.e., in your Location table, the Region column is an Integer value, representing one of your Regions. Then create a Regions table that list your region name only once. Thus the Location table just references an index (or Foreign Key) to your Regions table.

For example: your Regions table is something like this:

• id=1, regionname=North America
• id=2, regionname=South America
• id=3, regionname=Central America
• id=4, regionname=Asia/Pacific Islands

Then, your Locations table just indexes this:

• id=1, region=1
• id=2, region=2
• id=3, region=3
• id=4, region=4
• id=5, region=2
• id=6, region=1

Here is a simple, if crude, example:

from elixir import *

metadata.bind = "sqlite:///"

class Regions(Entity):    
    regionname = Field(String(255))

class Location(Entity):    
    region = ManyToOne('Regions')

setup_all()
create_all()

#Create the region names:
na_temp = Regions(regionname="North America")
sa_temp = Regions(regionname="South America")
ca_temp = Regions(regionname="Central America")
ap_temp = Regions(regionname="Asia/Pacific Islands")
session.commit()

#Create links to each region in the location table:
northamerica = Location(region=na_temp)
southamerica = Location(region=sa_temp)
centamerica = Location(region=ca_temp)
asiapacific = Location(region=ap_temp)
anotherarea = Location(region=sa_temp)
yetanotherarea = Location(region=na_temp)
session.commit()

#Get all items from the Location table:
locations = Location.query.all()

#Display the contents of the Location table, and lookup the name from the Regions table
for place in locations:
    print "Location table id: {}".format(place.region_id)    
    print "Lookup region name: {}".format(Regions.get_by(id=place.region_id).regionname)
    print

There is more than on way to do this, this is just my approach; I'm not the strongest Python programmer you'll meet.