Just a general question, but is there any particular reason why the naming conventions for Hub and Link table hash keys cannot be different?
Normally we would use a similar naming convention, for example, a customer hub table might have a hash key called HK_CUSTOMER, whereas an order line link table may have a hash key called HK_ORDER_LINE. In this example we have a common HK_ prefix. Is there any reason why we could not have alternative conventions, something like HHK_CUSTOMER and LHK_ORDER_LINE, where the hash key prefixes differ for hub and link tables? Why might we want to do this?
One particular scenario that occurred to me is if we wanted similar names for a hub and link table. I'll give an example. Say we have a Project concept and within each project a number of Work Items. We model this as Project and Work Item hub tables. The question then is what do we call the link table that relates them. I really want to call it LINK_WORK_ITEM because that is the unit of work for the link so let's go with that. If we use the same naming convention for hub and link tables then the HUB_WORK_ITEM and LINK_WORK_ITEM tables would both contain a hash key named HK_Work_Item which of course cannot happen as the LINK_WORK_ITEM would contain two fields with the same name, one the primary hash key the other a foreign key to the HUB_WORK_ITEM table.
One solution is to change the name of the link table to ensure the corresponding hash key is uniquely named. Alternatively we use different naming conventions for the respective hub and link hash keys to distinguish them as shown below:
What is the generally accepted convention in this scenario?