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", > "<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 > > > -- Andrew Solomon Mentor@Geekuni http://geekuni.com/ http://www.linkedin.com/in/asolomon
