SV: Inhomogeneous data in a database
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 Beckermailto:asb.c...@gmail.com
Skickat: 10.4.2015 23:56
Till: Andrew Solomonmailto:and...@geekuni.com
Kopia: beginners@perl.orgmailto: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, htmlbodyh1Start
Page/h1/body/html)~,
q~INSERT INTO html_contents VALUES (2, Title n, htmlbodyh1This
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
Re: Inhomogeneous data in a database
Hi Alex
Another option: replace `(navigation.target_type, navigation.target_id)`
with `(navigation.html_target_id, navigation.fancyscript_target_id)` only
one of which is defined for any given row, but both of which have foreign
key constraints when they are defined.
In answer to a subset of your questions:
Is there a standard approach for this kind of database table construct?
Not that I'm aware of.
Is there a best practice?
I think this discussion responds to that question.
http://dba.stackexchange.com/questions/15388/where-should-you-define-foreign-keys
As you'll see from the responses, the context of an application has a major
influence on the decision made.
I hope that helps!
Andrew
On Sat, Apr 11, 2015 at 8:17 AM, Tommi Helander tommi.helan...@outlook.com
wrote:
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,
htmlbodyh1Start Page/h1/body/html)~,
q~INSERT INTO html_contents VALUES (2, Title n,
htmlbodyh1This 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
Need explanation of code
Could someone please explain the difference between:
%{$self-{'DATA'}} = () }
and
$self-{'DATA'} = {}
I was told that they are equivalent, but they're not. One works and
the other doesn't, so they must be different. Here's the context:
sub empty_db {
my $self = shift;
if ($self-{'USEDBM'} eq 'sql') {
$self-{'SQL'}-do(DELETE from $self-{'DB'}) or
$self-{'ERRMSG'} .= $DBI::errstr and return;
}
else { %{$self-{'DATA'}} = () }
# else { $self-{'DATA'} = {} } # This does nothing
}
Thanks,
Frank
SurfShopCART 2
http://www.surfshopcart.com/u/demo
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
