Oh yes, Brandon read my mind. That was what I meant.
Think of the view "navigation" as an abstraction layer for your data, rather than it containing the data. That is one of the pros for views: You can abstract your complex, but robust data model beneath them and you don't have to care about the primary and foreign keys in your view, because the actual data tables take care of them. Thanks Brandon for the examples. Regards, Tommi ________________________________ Från: Brandon McCaig<mailto:bamcc...@gmail.com> Skickat: 16.4.2015 17:56 Till: Alex Becker<mailto:asb.c...@gmail.com> Kopia: Tommi Helander<mailto:tommi.helan...@outlook.com>; beginners@perl.org<mailto:beginners@perl.org> Ämne: Re: Inhomogeneous data in a database Alex: On Wed, Apr 15, 2015 at 11:27:41PM +0200, Alex Becker wrote: > Hi! Hello, > Thank you for your feedback. I also asked some colleagues about > it. We agreed that the view approach would be the nice thing. > > However, I do not know if I did it the right way / if I > understood t correctly. > > Given the table definition from my last mail, I added a view as > follows: > > CREATE VIEW v_navigation AS > > SELECT content_types.content_type_id as target_type_id, > > fancy_scripts.script_id as target_id > > FROM content_types > > JOIN fancy_scripts ON content_types.ident = "script" > > UNION > > SELECT content_types.content_type_id as target_type_id, > > html_contents.content_id as target_id > > FROM content_types > > JOIN html_contents ON content_types.ident = "page" > > > A table for the content types was also added. content_types is > a set of: {<1,page>,<2,script>} I think that what Tommi had in mind was something more like this (excuse the T-SQL, but it's what I'm most familiar with): navigation 1 1 / \ 0|1 0|1 html_navigation script_navigation 1 1 | | 1 1 html_contents fancy_scripts CREATE TABLE navigation( navigation_id INT NOT NULL PRIMARY KEY, position INT NOT NULL UNIQUE, link_text VARCHAR(45) NOT NULL, ); CREATE TABLE html_navigation( navigation_id INT NOT NULL PRIMARY KEY CONSTRAINT FK_html_navigation_navigation FOREIGN KEY REFERENCES navigation(navigation_id), content_id INT NOT NULL CONSTRAINT FK_html_navigation_html_contents FOREIGN KEY REFERENCES html_contents(content_id) ); CREATE TABLE script_navigation( navigation_id INT NOT NULL PRIMARY KEY CONSTRAINT FK_html_navigation_navigation FOREIGN KEY REFERENCES navigation(navigation_id), script_id INT NOT NULL CONSTRAINT FK_html_navigation_fancy_scripts FOREIGN KEY REFERENCES fancy_scripts(script_id) ); CREATE TABLE target_type( target_type_id INT NOT NULL PRIMARY KEY, name VARCHAR(10) NOT NULL UNIQUE ); CREATE VIEW v_navigation AS SELECT a.navigation_id, a.position, a.link_text, /*or use foreign key if you wish*/ "content" AS target_type, c.content_id AS target_id FROM navigation a, target_type b INNER JOIN html_navigation c ON a.navigation_id = c.navigation_id WHERE b.name = 'content' UNION SELECT a.navigation_id, a.position, a.link_text, b.target_type_id AS target_type, c.script_id AS target_id FROM navigation a, target_type b INNER JOIN html_navigation c ON a.navigation_id = c.navigation_id WHERE b.name = 'script'; Untested. Meant purely to illustrate the idea. Let me know if you have any questions. Regards, -- Brandon McCaig <bamcc...@gmail.com> <bamcc...@castopulence.org> Castopulence Software <https://www.castopulence.org/> Blog <http://www.bambams.ca/> perl -E '$_=q{V zrna gur orfg jvgu jung V fnl. }. q{Vg qbrfa'\''g nyjnlf fbhaq gung jnl.}; tr/A-Ma-mN-Zn-z/N-Zn-zA-Ma-m/;say'
