Hi!
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>}
But...
How do I relate the navigation table entries to this view?
I always thought of views as some kind of prepared and optimized select
statement (e.g. because one could store complex queries across multiple
tables as view).
And there is no unique primary key (PK) in the view. All the other tables
have a PK that can be references by a foreign key (FK) constraint.
How do I create such a constraint for the navigation table?
Can I define the following statement as a constraint?
"If there exists a pair of <CONTENT_TYPE_ID,TARGET_ID> in the view
v_navigation, it is OK to have the record
<some_navigation_id,some_position,a-link-text,CONTENT_TYPE_ID,TARGET_ID> in
the navigation table"
Sorry if the question is too complicated. Please feel free to tell me. I
will then try to rephrase.
Best regards,
Alex
2015-04-11 9:17 GMT+02:00 Tommi Helander <tommi.helan...@outlook.com>:
> Hi Alex,
>
> Without going into details about your data model in general, then in your
> case I would probably create two different navigation tables: one for html
> and one for scripts. Then I would be able to create reliable foreign key
> constraints. Then, if I wanted a convenient way of selecting all kinds of
> navigation records from Perl, I would create a view that combines the
> different kinds of navigation tables and contain the kind of type field and
> id field that you suggested. You see, this would make it possible to assign
> constraints on the underlying data model, without complicating the way it's
> selected.
>
> On the other hand, I usually work with larger databases and more complex
> data models, so this might be a little overkill in your case. But anyway,
> this what I consider good practice. Others may have different opinions.
>
> Hope this helps!
>
> Best regards,
> Tommi
> ------------------------------
> Från: Alex Becker <asb.c...@gmail.com>
> Skickat: 10.4.2015 23:56
> Till: Andrew Solomon <and...@geekuni.com>
> Kopia: beginners@perl.org
> Ämne: Re: Inhomogeneous data in a database
>
> Hi Andrew,
>
> I'm not interested in ways how to get the data out of the database. I'm
> usually using DBIx::CLass because I'm too lazy to write SQL.
>
> To provide you an example of a table and the question I have, I did it
> anyway :). This would be the example case:
>
> #!perl
>
> use strict;
> use warnings;
> use DBI;
> use DBD::SQLite;
>
> my $db_file = 'test.sqlite';
> unlink $db_file if -e $db_file;
>
> my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file", '', '') or die("Could
> not connect to database $db_file");
>
> my @stmts = (
> q~CREATE TABLE "html_contents" (
> "content_id" INTEGER PRIMARY KEY NOT NULL,
> "title" varchar(255) NOT NULL,
> "html" TEXT NOT NULL
> );~,
> q~CREATE TABLE "fancy_scripts" (
> "script_id" INTEGER PRIMARY KEY NOT NULL,
> "perl_module" varchar(255) NOT NULL
> );~,
> q~CREATE TABLE "navigation" (
> "navigation_id" INTEGER PRIMARY KEY NOT NULL,
> "position" INTEGER UNIQUE NOT NULL,
> "link_text" carchar(45) NOT NULL,
> "target_type" varchar(45) NOT NULL,
> "target_id" INT NOT NULL
> );~,
> q~INSERT INTO html_contents VALUES (1, "Title1",
> "<html><body><h1>Start Page</h1></body></html>")~,
> q~INSERT INTO html_contents VALUES (2, "Title n",
> "<html><body><h1>This is Mario!</h1></body></html>")~,
> q~INSERT INTO fancy_scripts VALUES (1, "CMS::FancyScript")~,
> q~INSERT INTO fancy_scripts VALUES (2, "CMS::Guestbook")~,
> q~INSERT INTO navigation VALUES (1, 1, "Home", "html_contents", 1)~,
> q~INSERT INTO navigation VALUES (2, 3, "About Me", "html_contents",
> 2)~,
> q~INSERT INTO navigation VALUES (3, 2, "Contact", "fancy_scripts", 1)~,
> );
>
> foreach my $sql ( @stmts ) {
> $dbh->do($sql) or die("Something bad happened doing SQL: " .
> $dbh->errstr);
> }
>
> print "> done\n";
> exit(0);
>
>
> The issue is:
> Usually, when reading articles about database normalization, everyone
> talks about putting information into separate tables and referencing them
> (e.g. using foreign keys).
> I did so. Every information has a dedicated table (except for the
> target_type column for the sake of example simplicity).
>
> But the value of the target_id column cannot be defined as a foreign key.
> Because it might be an ID of the table fancy_scripts or one of the table
> "html_contents". I know which table to use depending on the value of
> target_type.
>
> I even could create 2 views to denormalize the tables for fast access. One
> view would reflect the navigation links for fancy scripts, and one would
> reflect the navigation links for html contents.
>
> But: usually, when fetching a navigation structure of some kind, you fetch
> *all* the links.
>
> It can be done. I can code it the way I want it to work.
> But I'm *not sure* if it is the right thing to do (and no, I don't really
> want to start a discussion about TIMTOWTDI, Perl's philosophy, why Pyhton
> wuld be better for this approach, or what people think about this
> particular sentence in general).
>
> Maybe I can rephrase my inital questions:
>
> > Is there a standard approach for this kind of database table construct?
> Is it the right thing to do?
>
> > A coding recommendation?
> Is there a best practice?
>
> > Or am I doing it completely wrong?
> Should I store the information in another fashion?
>
> Thanks for your answers & best regards,
> Alex
>
> 2015-04-10 21:51 GMT+02:00 Andrew Solomon <and...@geekuni.com>:
>
> Hi Alex
>
> Maybe you could give us a bit more info on the `html_contents` and
> `fancy_scripts` tables, because my inclination would be just to have a
> simple `contents` table.
>
> This table would have three fields: (`id,`content_type`, `blob`) where:
> * `content_type` is "HTML", "CGI" or "Module" (or rather, ids pointing to
> the `target_type` table);
> * `blob` is a base64 encoding of a structure containing both the data
> (HTML, CGI or module) and any metadata such as URL parameters, class
> attributes etc.
>
> I would then be using DBIx::Class to access the table and have methods
> on the Schema to decode and make use of `blob` appropriately depending on
> the `content_type`.
>
> Perhaps it would be easier to have two fields (`metadata`, `data`)
> instead of `blob` but you get the idea.
>
> Let me know if that's clear as mud, or I've missed the point altogether:)
>
> Andrew
>
>
>
> On Fri, Apr 10, 2015 at 6:04 PM, Alex Becker <asb.c...@gmail.com> wrote:
>
> Hi!
>
> This is not directly a Perl question, but since Perl is the only language
> I code in, I hope you could help me out.
>
> I have a database for a very simple Perl-based Web CMS.
> The CMS knows only 2 types of content: a HTML web site or a Perl module
> executing some fancy CGI script.
>
> So let's assume I have 3 tables:
> a) html_contents
> b) fancy_scripts
> c) navigation
>
> HTML contents is trivial. It's an excel-sheet-like table with a title
> and the HTML code.
>
> Fancy scripts work the same, except that it's maybe the URL to the CGI
> script, or a class name.
>
> Navigation however can hold 2 kinds of content: a reference to a row in
> the HTML contents table or a reference to one of those fancy scripts.
>
> How do I do that?
> My current approach is the following:
>
> Make the navigation table have 3 columns:
> <id, target_type, target_id>
>
> target_type is an identifier for the kind of thing I reference. In the
> example, it could be fancy_script or content.
>
> But now I get a bad feeling because I can't really make the column
> target_it a foreign key that really is a reference to one of those tables
> (scripts or HTML).
> I usually always do it that way. I even normalize the target_type so
> that I have an extra table for target_types with a target type ID and an
> ident.
>
> Is there a standard approach for this kind of database table construct?
> A coding recommendation?
> Or am I doing it completely wrong?
>
> Best regards,
> Alex
>
>
>
>
> --
> Andrew Solomon
>
> Mentor@Geekuni http://geekuni.com/
> http://www.linkedin.com/in/asolomon
>
>
>
