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
>
