Tuesday, September 27, 2011

Cascading dropdowns and Primary Key field name

Best practice in Alpha Five is to name all primary keys with the table name plus "_id". E.g., the PK for the Product table would be product_id. This may be obvious to those of you who always followed that practice. But there are many developers who name their primary key "ID" for all tables (I mean I was one up to just a few days ago).

Case 1 - when creating relationships between tables in, for instance, a Linked Content Area, Alpha Five anticipates that the PK in the parent is named the same as the FK in the child. It automatically make that link for you if you have named them the same.

Case 2 - I just learned it is mandatory to follow this practice if you expect cascading dropdown features to work properly (cascade is built in to Alpha Five dropdown controls as a simple checkbox option).

If the PK and the FK are named different, the cascade will not work. Here is a graphic showing my tables names for a simple State-City cascading dropdown. Notice my field name of State_Id is the sae for both the parent and child tables.

Note that it is only important in this case that the State_Id field name is the same. I purpously did not name the PK for the City table as City_Id just to show it is not mandatory for this cascading dropdown to work. But of course, I would name it City_Id following my new rule of always naming the primary key as table_id.

No comments: