Representing several alternative tables as one?

Question

First off, I'm stuck in the visual editor of workbench, so there's that.

Assume a database containing florists and academics. The two are functionally distinct, to the point that I am required to store them as separate tables.

At the same time though, the florists and academics can be friends. We have academics with academic friends, florists with florist friends, academics with florist friends, the works. And in the same vein, they do all sorts of stuff that are interchangeable between academics and florists. So, I need to be able to handle both academics and florists interchangeably, too. What is the most elegant approach for that?

I considered adding a table with a single primary key, adding it as a foreign key on both florists and academics, and then using that as a way to treat them interchangeably. But that adds a lot, so if there's a tidier way, I'd like it! Any suggestions?

Your idea is reasonable. You need a "super-entity" that encompasses both florists and academics to use for other tables. — Gordon Linoff, Mar 24 '19 at 18:40
Yes, like Gordon said: Florists and academics are both humans. They share a lot of properties. You would put those in the "human" table. Then for the properties specifically for florists you make a special purpose table, and similary for the academics. Finally you add a foreign key, to the "human" table, in these two tables. — KIKO Software, Mar 24 '19 at 18:56

score 0 · Accepted Answer · answered Mar 24 '19 at 18:47

0

the appropriate answer to your problem is either number two or three from here:

How can you represent inheritance in a database?

You just need to identify, which one suits your specific problem better.

answered Mar 24 '19 at 18:47

Tarek Salha

307
3
12

Hmmm, that's interesting but not terribly helpful. The third approach is the one I felt was cumbersome, the second is the one I'd run if I could make it work, but it doesn't explain how I could. – Thanos Maravel Mar 24 '19 at 19:05
Ok, let me look at that today in the evening a little closer. Then I may give you an example. – Tarek Salha Mar 26 '19 at 16:20
Thanks, but we opted for the inheritance paradigm. It came out less messy than I thought I'd be. – Thanos Maravel Mar 27 '19 at 21:41
But, by all means, do so for posterity. Just don't rush yourself. – Thanos Maravel Mar 27 '19 at 22:02

Representing several alternative tables as one?

1 Answers1