Proposed Database Layout

Opening Comments:

Greetings,

The purpose of this website is to allow people with food allergies and intolerances to be able to find food that is safe for them to eat, and to find other products that are safe for them to use.

In order to achieve this, I hope to collect the information in a database so that people will be able to ask the database for things that match their individual needs.

I've spent some time designing the database and would like to get some feedback.  If I missed something that is needed, or if there is some flaw in the design, hopefully someone can warn me about it before I progress too far.

With the database documentation posted here, another, somewhat morbid benefit comes about.  If I get hit by the proverbial truck, someone else can take over from where I left off.

My current plans are to start creating and testing the database while I'm waiting for comments on the design.  If no major changes are required, I'll start designing the input and query form web pages.

If you have any comments or suggestions please mail them to: DBA@AllergyFreeFood.org.

Documentation:

For starters, look at the graphical overview of the relationship between the tables. (in separate window)

I believe that most users of the website will spend their time looking for prepared foods that are safe to eat and for places like restaurants that can accommodate their specific food allergy or intolerance.  With this in mind, the Item and Establishment tables are probably the "center" of the database.

While these tables are dissimilar, they do share some common ideas and information.  This shared information is likley to be the focus of most of the searches done on the website.  Most users will start their searches by inputing a list of things that are unsafe for them to consume.  These Allergens and Allergen Categories are represented in the Item table by the Allergens_Present and Allergens_Absent fields.  In the Establishment table they are represented by the Can_Accommodate and Cant_Accommodate fields.

Since there is potential for a search concentrating only on specific allergens to return a large number of results, Availability information can also be tied to the Item and Establishment tables. Users will want to know: "Can these restaurants or foods be found where I live or where I'm going to visit?"

Most of the other tables in the database are there to give the users greater options for refining their searches or for helping them do additional research on their own.

Since I only have a limited amount of time and knowledge, this site will not be successful unless other people contribute.  This is the reason that all the tables have connections to the Food Detective table.  In the tables, I'm keeping track of who created the entries and who last updated them.  In some cases, I've left space for a food detective to note that they have confirmed the observations of fellow sleuths.

For more detailed information about the table design, see the documention for any of the following:

Allergen Table,
Allergens Table,
Allergen_Category Table,
Availability Table,
Company Table,
Cuisine Table,
Establishment Table,
Establishment_Type Table,
Food Detective,
Item Table,
Item_Category Table,
Location Table,
Region Table.
I'm also hoping for some help in developing default values for some of the tables.  These include:
Allergens and Allergen Categories,
Cuisine Types,
Establishment Types,
Item Categories,
Regions of the USA and the World.
Again, please send suggestions and helpful hints to the provided email address.

Thanks,

James T. Savidge.

Send comments and suggestions to:
DBA@AllergyFreeFood.org

Home | Documentation

Last updated on 12/2/00