Multi-purpose tables joined using reference keys

Classic Servoy
1

I’m considering how I want to implement my “addresses” table in a new solution, and am debating a little bit different strategy. I thought I’d post it for comment, and to see how others are handling similar situations.

So, problem is, I want to factor addresses into their own table. Something like:

Addresses

  • address_id
  • address_type
  • address_name
  • address_street
  • address_city
    etc.

I’ve got multiple different tables that will have n-m relations to addresses. Companies, Projects, etc. Ok, nothing new here, I know.

I do not want to create intermediary join tables for all these relations. Yuck. I’d rather do a polymorphic relation on the address table, where I reference the ID and reference type for the other side of the relation. So I would add these fields to the address table:

  • reference_id
  • reference_type

Then my relation from Companies to Addresses would be:

company_id = reference_id
“companies” = reference_type

Right now, I can’t define a static value on a relation, however. Therefore, I see two way to get this done –

  1. define a global for each static relation key (globals.table_name_companies, or something) and use that in the relation.

  2. define a stored calculation on my tables “table_id” that redundantly stores the name of the table in every record, and use that field in the relation.

On the plus side, setting this up makes is easy to display and reference my related addresses, without having to go through an extra join relation.

Any ideas? Anyone doing anything similar to this in a different way that I’m missing?

Thanks, g.

2

agiletortoise:
2) define a stored calculation on my tables “table_id” that redundantly stores the name of the table in every record, and use that field in the relation.

You don’t have to have a stored calculation for this. As long as you use it on the left side of the relation you can use an unstored calculation (just like you can use a global for this).

Hope this helps.

3

Isn’t the number of connections to the address table somehow limited? You have projects, people, … that have addresses. Couldn’t you create a few foreign keys in addresses like project_id? I have been using both of your techniques and they worked fine. I also the approach with a few foreign keys in certain situations. Just go for one. They all work.

4

You don’t have to have a stored calculation for this. As long as you use it on the left side of the relation you can use an unstored calculation (just like you can use a global for this).

Oh yes! I have overseen that part. Some of my tables have an (unstored) calc that says

return 'table_name';
5

but if you do this:

company_id = reference_id
"companies" = reference_type

that address can’t be used than, for example your projects, right??
so the only way this works, that you have an explicit split in you addresses.

6

Harjo, I am not sure if I understand you right. The idea was to create a relation that points from

unstored_calc with table name → column “owner” (or whatever)
primare key of table → column “pk_owner”

You can create such a relation for projects, companies etc. Is that what you meant?

7

I am using a blend of Patrick and Robert’s methods with a global in the relation (set from a table of tables within the calling method … works fine … as do multiple fk’s if you prefer a more traditional pk-fk model for your db model.

Best,

Michael

8

Harjo is pointing out that Greg will lose the opportunity to reuse the same address record more than once in not using join tables.

I am sure that Greg is aware of this in his choice to disregard using join tables and so to Greg I say that if you are happy that this schema supports what you need to represent then my preference would be to do it by replicating separate foreign keys in the address table as follows:

Addresses

  • address_id
  • address_type
  • address_name
  • address_street
  • address_city
  • fk_companies_id
  • fk_projects_id
  • fk_person_id
    etc, etc

Cheers
Harry

9

That’s what I meant…

Couldn’t you create a few foreign keys in addresses like project_id?

10

Ok, I’m dense. Don’t know why I didn’t realize you could use an unstored calc in a relationship…that makes my life easier.

Yes, I know I can’t re-use the same address in this scenario – but, frankly, I’ve never found addresses turn out to be that reusable anyway. Users always want to add that special marking for that one order, or such – and want that permanent record of what was attached to an order/invoice, etc.

On the multiple fields for the keys, I personally prefer the cleanliness I of my approach in this case, because I’d never have multiple relations to the same address. I do use that approach for other things I cross link, like notes/to-dos/activities.

Thanks for the tips…g.