Table design - virtual column question(?)

[Andrzejek]

I have my PROJECT as a concatenation of some information and it looks like this:
[pre]
2016-Alaska-3(507)--AL-XYZ
2017-California-7(70)--CA-ABC
[/pre]
So let’s say I have a table with US_States:[pre]
ID State Abbrv
1 Alabama AL
2 Alaska AK
3 Arizona AZ
4 Arkansas AR
5 California CA
6 Colorado CO
...[/pre]
And I want to keep all the data for my PROJECT in a TableA, and create a lot of other tables that would refer to this table by PK-FK relation.
Intrigued by virtual columns in Oracle 11 I hoped I can employ them into concatenated PROJECT column:
[pre]
ID YEAR STATE NO1 NO2 ED PROJECT
1 2016 [blue]2[/blue] 3 507 XYZ 2016-[blue]Alaska[/blue]-3(507)--[blue]AL[/blue]-XYZ
2 2017 [blue]5[/blue] 7 70 ABC 2017-[blue]California[/blue]-7(70)--[blue]CA[/blue]-ABC
[/pre]
But turns out with virtual columns I cannot call data from other table(s), I have to use data from the same table where my virtual column is.
So what would be the best way to have TableA with all the information I need along with the full PROJECT field? I don’t really want to copy State and its abbreviation to my TableA from US_STATES table, I would rather use FK (TableA.STATE) to PK (US_STATES.ID) relation.
I know I can create a View with the data from TableA and US_STATES and have all the data I need, but then I need to connect all other tables with TableA.ID, but get the PROJECT column from this View.

Any suggestions?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[johnherman]

Materialized view?

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[ChrisHunt]

I know I can create a View with the data from TableA and US_STATES and have all the data I need, but then I need to connect all other tables with TableA.ID, but get the PROJECT column from this View.

No, you just create a view something like this:

SELECT a.*,
       a.year||'-'||s.state||'-'||a.no1||'('||a.no2||')--'||s.abbrv||'-'||a.ed
FROM   TableA a
INNER JOIN US_States s   -- or OUTER JOIN if a.state is nullable
ON s.id = a.state

And then just use this view wherever you would have used TableA.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd