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'
signature.asc
Description: Digital signature
