[Dbix-class] Relationship question/database structure
Hi All, I'm trying to use an existing schema wherein I have a generic table called 'Note' containing - you guessed it, notes!. The thing is that these notes may be associated with many other types of data and therefore tables in my database. They may be related to a request, user, device, etc. I am sure that this is a fairly common use case, but don't see much in the docs or on the list. The question is two-fold: What's the best structure, and how do we go about defining the DBIC rels? Thanks, Steve ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Relationship question/database structure
A relational database cannot easy express foreign key constraints to multiple tables from a single column. In fact I am sure you are using the artifact of identical underlying native types to overload a single column to contain multiple different types of data. That is to say that a reference to a transaction entry is not the same as a reference to a customer service event. But here you have placed both types of data in the same column, permitted only because their underlying data types were identical and you are not making the db check foreign constraints. Now you want to express this sloppy schema in the class structure and run into the fact it is going to be sloppy there too. You really should fix the schema to be tight and checked but this is not an ideal world. There isn't anything stopping you from adding multiple belongs to directives to your notes result class other than it implies that all the notes are related to all the tables - semantically absurd but as long as you use the proper constraints when joining the result will be what you want. It's just... sloppy. No offense intended and I do understand you are working under constraints I am not familiar with that may limit your solutions. Trying to be helpful, Daivd On Mon, Jan 10, 2011 at 12:58 PM, Skylos sky...@gmail.com wrote: A relational database cannot easy express foreign key constraints to multiple tables from a single column. In fact I am sure you are using the artifact of identical underlying native types to overload a single column to contain multiple different types of data. That is to say that a reference to a transaction entry is not the same as a reference to a customer service event. But here you have placed both types of data in the same column, permitted only because their underlying data types were identical and you are not making the db check foreign constraints. Now you want to express this sloppy schema in the class structure and run into the fact it is going to be sloppy there too. You really should fix the schema to be tight and checked but this is not an ideal world. There isn't anything stopping you from adding multiple belongs to directives to your notes result class other than it implies that all the notes are related to all the tables - semantically absurd but as long as you use the proper constraints when joining the result will be what you want. It's just... sloppy. No offense intended and I do understand you are working under constraints I am not familiar with that may limit your solutions. Trying to be helpful, Skylos On Jan 10, 2011 11:59 AM, Steve st...@matsch.com wrote: Hi All, I'm trying to use an existing schema wherein I have a generic table called 'Note' containing - you guessed it, notes!. The thing is that these notes may be associated with many other types of data and therefore tables in my database. They may be related to a request, user, device, etc. I am sure that this is a fairly common use case, but don't see much in the docs or on the list. The question is two-fold: What's the best structure, and how do we go about defining the DBIC rels? Thanks, Steve ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk -- David Ihnen Voice contact (562) 743-1807 ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Relationship question/database structure
You're not thinking specifically enough. The column which has the ID in it
is being used to store disparate types of information - a transaction note
identifier is a DIFFERENT type than a customer service event identifier -
but they're in the *same column*. *hits the penalty buzzer* bad dba. ;)
The DB is almost assuredly not constraint checking this column to make sure
that the proper record occurs in the proper foreign table. *buzz* This is
sloppy db design and is going to result in compromises in how you access it.
But relationships don't join on multiple columns.
As I said, you CAN create multiple belongs-to relationships, and as long as
your tableName column is included in your query (which is sloppy because it
should be encapsulated) then you're going to get the result you want.
Something like:
$caseresultset-search( { 'notes.TableName' = 'cases' }, { join = notes }
);
Also, you can chain resultsets - using a technique to create something like
this in your notes class
sub case_notes {
return shift-search( { TableName = 'case' } );
}
And query through the chain to find notes for the case.
my $cases = ...ResultSet('cases')-search( { case_id = 5 }
)-notes-case_notes;
(which is sloppy because it should be encapsulated in the relationship
without you being explicit about it)
What you really have constructed is an abstract data store for instances of
note data, and each type of note data will have its *own* class - its not
immediately obvious to me if you can actually express that in DBIx::class -
I'm sure that eventually we will be able to do so. Its complicated though,
and involves the idea of constraints on subqueries which is just outside the
purveyance of sql::abstract to my understanding of the system.
Whatever way you manage to munge DBIx::Class into handling this abstract
data store for note instance data i don't think its going to be as clean as
anybody would like, probably a little sloppy and/or ugly. Its not the level
of problem the object relational mapper was designed to solve, though i'm
sure it will eventually be capable. Patch? ;) (okay, that would probably
require a pretty significant level of changes...)
David
On Mon, Jan 10, 2011 at 1:18 PM, Steve st...@matsch.com wrote:
I was unclear... sorry for that. The current 'note' table has two columns:
'TableName' and 'TableId', where the 'TableName' field is an abbreviation
for the name of the table the note is related to, and the 'TableId' is the
record within that table.
So, it is in fact a multi-column join between the note table and several
other tables, each with their own 'TableName'.
Steve
On 1/10/2011 1:07 PM, David Ihnen wrote:
A relational database cannot easy express foreign key constraints to
multiple tables from a single column. In fact I am sure you are using the
artifact of identical underlying native types to overload a single column to
contain multiple different types of data. That is to say that a reference
to a transaction entry is not the same as a reference to a customer service
event. But here you have placed both types of data in the same column,
permitted only because their underlying data types were identical and you
are not making the db check foreign constraints. Now you want to express
this sloppy schema in the class structure and run into the fact it is going
to be sloppy there too.
You really should fix the schema to be tight and checked but this is not
an ideal world.
There isn't anything stopping you from adding multiple belongs to
directives to your notes result class other than it implies that all the
notes are related to all the tables - semantically absurd but as long as you
use the proper constraints when joining the result will be what you want.
It's just... sloppy.
No offense intended and I do understand you are working under constraints
I am not familiar with that may limit your solutions.
Trying to be helpful,
Daivd
On Mon, Jan 10, 2011 at 12:58 PM, Skylos sky...@gmail.com mailto:
sky...@gmail.com wrote:
A relational database cannot easy express foreign key constraints
to multiple tables from a single column. In fact I am sure you
are using the artifact of identical underlying native types to
overload a single column to contain multiple different types of
data. That is to say that a reference to a transaction entry is
not the same as a reference to a customer service event. But here
you have placed both types of data in the same column, permitted
only because their underlying data types were identical and you
are not making the db check foreign constraints. Now you want to
express this sloppy schema in the class structure and run into the
fact it is going to be sloppy there too.
You really should fix the schema to be tight and checked but this
is not an ideal world.
There isn't anything stopping you from adding multiple belongs to
directives to your notes
Re: [Dbix-class] Relationship question/database structure
Reading between the lines, is it your opinion that having several
differently named tables with identical fields is better than the
current approach?
On 1/10/2011 1:34 PM, David Ihnen wrote:
You're not thinking specifically enough. The column which has the ID
in it is being used to store disparate types of information - a
transaction note identifier is a DIFFERENT type than a customer
service event identifier - but they're in the *same column*. *hits
the penalty buzzer* bad dba. ;) The DB is almost assuredly not
constraint checking this column to make sure that the proper record
occurs in the proper foreign table. *buzz* This is sloppy db design
and is going to result in compromises in how you access it.
But relationships don't join on multiple columns.
As I said, you CAN create multiple belongs-to relationships, and as
long as your tableName column is included in your query (which is
sloppy because it should be encapsulated) then you're going to get the
result you want. Something like:
$caseresultset-search( { 'notes.TableName' = 'cases' }, { join =
notes } );
Also, you can chain resultsets - using a technique to create something
like this in your notes class
sub case_notes {
return shift-search( { TableName = 'case' } );
}
And query through the chain to find notes for the case.
my $cases = ...ResultSet('cases')-search( { case_id = 5 }
)-notes-case_notes;
(which is sloppy because it should be encapsulated in the relationship
without you being explicit about it)
What you really have constructed is an abstract data store for
instances of note data, and each type of note data will have its *own*
class - its not immediately obvious to me if you can actually express
that in DBIx::class - I'm sure that eventually we will be able to do
so. Its complicated though, and involves the idea of constraints on
subqueries which is just outside the purveyance of sql::abstract to my
understanding of the system.
Whatever way you manage to munge DBIx::Class into handling this
abstract data store for note instance data i don't think its going to
be as clean as anybody would like, probably a little sloppy and/or
ugly. Its not the level of problem the object relational mapper was
designed to solve, though i'm sure it will eventually be capable.
Patch? ;) (okay, that would probably require a pretty significant
level of changes...)
David
On Mon, Jan 10, 2011 at 1:18 PM, Steve st...@matsch.com
mailto:st...@matsch.com wrote:
I was unclear... sorry for that. The current 'note' table has two
columns: 'TableName' and 'TableId', where the 'TableName' field is
an abbreviation for the name of the table the note is related to,
and the 'TableId' is the record within that table.
So, it is in fact a multi-column join between the note table and
several other tables, each with their own 'TableName'.
Steve
On 1/10/2011 1:07 PM, David Ihnen wrote:
A relational database cannot easy express foreign key
constraints to multiple tables from a single column. In fact
I am sure you are using the artifact of identical underlying
native types to overload a single column to contain multiple
different types of data. That is to say that a reference to a
transaction entry is not the same as a reference to a customer
service event. But here you have placed both types of data in
the same column, permitted only because their underlying data
types were identical and you are not making the db check
foreign constraints. Now you want to express this sloppy
schema in the class structure and run into the fact it is
going to be sloppy there too.
You really should fix the schema to be tight and checked but
this is not an ideal world.
There isn't anything stopping you from adding multiple belongs
to directives to your notes result class other than it implies
that all the notes are related to all the tables -
semantically absurd but as long as you use the proper
constraints when joining the result will be what you want.
It's just... sloppy.
No offense intended and I do understand you are working under
constraints I am not familiar with that may limit your solutions.
Trying to be helpful,
Daivd
On Mon, Jan 10, 2011 at 12:58 PM, Skylos sky...@gmail.com
mailto:sky...@gmail.com mailto:sky...@gmail.com
mailto:sky...@gmail.com wrote:
A relational database cannot easy express foreign key
constraints
to multiple tables from a single column. In fact I am sure you
are using the artifact of identical underlying native types to
overload a single column to contain multiple different types of
data. That is to say that a reference to a transaction
entry is
not the same
Re: [Dbix-class] Relationship question/database structure
The canonical approach is:
orders
line_items
persons
notes
orders_x_notes
line_items_x_notes
persons_x_notes
So, each type has an xref table to the notes table. But, there's only
one notes table.
Rob
On Mon, Jan 10, 2011 at 13:47, Steve st...@matsch.com wrote:
Reading between the lines, is it your opinion that having several
differently named tables with identical fields is better than the current
approach?
On 1/10/2011 1:34 PM, David Ihnen wrote:
You're not thinking specifically enough. The column which has the ID in
it is being used to store disparate types of information - a transaction
note identifier is a DIFFERENT type than a customer service event identifier
- but they're in the *same column*. *hits the penalty buzzer* bad dba. ;)
The DB is almost assuredly not constraint checking this column to make sure
that the proper record occurs in the proper foreign table. *buzz* This is
sloppy db design and is going to result in compromises in how you access it.
But relationships don't join on multiple columns.
As I said, you CAN create multiple belongs-to relationships, and as long
as your tableName column is included in your query (which is sloppy because
it should be encapsulated) then you're going to get the result you want.
Something like:
$caseresultset-search( { 'notes.TableName' = 'cases' }, { join = notes
} );
Also, you can chain resultsets - using a technique to create something
like this in your notes class
sub case_notes {
return shift-search( { TableName = 'case' } );
}
And query through the chain to find notes for the case.
my $cases = ...ResultSet('cases')-search( { case_id = 5 }
)-notes-case_notes;
(which is sloppy because it should be encapsulated in the relationship
without you being explicit about it)
What you really have constructed is an abstract data store for instances
of note data, and each type of note data will have its *own* class - its not
immediately obvious to me if you can actually express that in DBIx::class -
I'm sure that eventually we will be able to do so. Its complicated though,
and involves the idea of constraints on subqueries which is just outside the
purveyance of sql::abstract to my understanding of the system.
Whatever way you manage to munge DBIx::Class into handling this abstract
data store for note instance data i don't think its going to be as clean as
anybody would like, probably a little sloppy and/or ugly. Its not the level
of problem the object relational mapper was designed to solve, though i'm
sure it will eventually be capable. Patch? ;) (okay, that would probably
require a pretty significant level of changes...)
David
On Mon, Jan 10, 2011 at 1:18 PM, Steve st...@matsch.com
mailto:st...@matsch.com wrote:
I was unclear... sorry for that. The current 'note' table has two
columns: 'TableName' and 'TableId', where the 'TableName' field is
an abbreviation for the name of the table the note is related to,
and the 'TableId' is the record within that table.
So, it is in fact a multi-column join between the note table and
several other tables, each with their own 'TableName'.
Steve
On 1/10/2011 1:07 PM, David Ihnen wrote:
A relational database cannot easy express foreign key
constraints to multiple tables from a single column. In fact
I am sure you are using the artifact of identical underlying
native types to overload a single column to contain multiple
different types of data. That is to say that a reference to a
transaction entry is not the same as a reference to a customer
service event. But here you have placed both types of data in
the same column, permitted only because their underlying data
types were identical and you are not making the db check
foreign constraints. Now you want to express this sloppy
schema in the class structure and run into the fact it is
going to be sloppy there too.
You really should fix the schema to be tight and checked but
this is not an ideal world.
There isn't anything stopping you from adding multiple belongs
to directives to your notes result class other than it implies
that all the notes are related to all the tables -
semantically absurd but as long as you use the proper
constraints when joining the result will be what you want.
It's just... sloppy.
No offense intended and I do understand you are working under
constraints I am not familiar with that may limit your solutions.
Trying to be helpful,
Daivd
On Mon, Jan 10, 2011 at 12:58 PM, Skylos sky...@gmail.com
mailto:sky...@gmail.com mailto:sky...@gmail.com
mailto:sky...@gmail.com wrote:
A relational database cannot easy express foreign key
constraints
to multiple tables from a single column. In fact I
Re: [Dbix-class] Relationship question/database structure
Yes. That was what i was typing basically when Mr Kinyon's message came
through. Quite right, sir, this is the sort of approach I would take as
well. Each column only has one type of data in it. There are more tables
for cross reference, but thats the hit you take for not putting multiple key
columns in the notes table or as an alternative creating separate notes
tables for each type.
David
On Mon, Jan 10, 2011 at 1:50 PM, Rob Kinyon rob.kin...@gmail.com wrote:
The canonical approach is:
orders
line_items
persons
notes
orders_x_notes
line_items_x_notes
persons_x_notes
So, each type has an xref table to the notes table. But, there's only
one notes table.
Rob
On Mon, Jan 10, 2011 at 13:47, Steve st...@matsch.com wrote:
Reading between the lines, is it your opinion that having several
differently named tables with identical fields is better than the current
approach?
On 1/10/2011 1:34 PM, David Ihnen wrote:
You're not thinking specifically enough. The column which has the ID in
it is being used to store disparate types of information - a transaction
note identifier is a DIFFERENT type than a customer service event
identifier
- but they're in the *same column*. *hits the penalty buzzer* bad dba.
;)
The DB is almost assuredly not constraint checking this column to make
sure
that the proper record occurs in the proper foreign table. *buzz*
This is
sloppy db design and is going to result in compromises in how you access
it.
But relationships don't join on multiple columns.
As I said, you CAN create multiple belongs-to relationships, and as long
as your tableName column is included in your query (which is sloppy
because
it should be encapsulated) then you're going to get the result you want.
Something like:
$caseresultset-search( { 'notes.TableName' = 'cases' }, { join =
notes
} );
Also, you can chain resultsets - using a technique to create something
like this in your notes class
sub case_notes {
return shift-search( { TableName = 'case' } );
}
And query through the chain to find notes for the case.
my $cases = ...ResultSet('cases')-search( { case_id = 5 }
)-notes-case_notes;
(which is sloppy because it should be encapsulated in the relationship
without you being explicit about it)
What you really have constructed is an abstract data store for instances
of note data, and each type of note data will have its *own* class - its
not
immediately obvious to me if you can actually express that in
DBIx::class -
I'm sure that eventually we will be able to do so. Its complicated
though,
and involves the idea of constraints on subqueries which is just outside
the
purveyance of sql::abstract to my understanding of the system.
Whatever way you manage to munge DBIx::Class into handling this abstract
data store for note instance data i don't think its going to be as clean
as
anybody would like, probably a little sloppy and/or ugly. Its not the
level
of problem the object relational mapper was designed to solve, though
i'm
sure it will eventually be capable. Patch? ;) (okay, that would
probably
require a pretty significant level of changes...)
David
On Mon, Jan 10, 2011 at 1:18 PM, Steve st...@matsch.com
mailto:st...@matsch.com wrote:
I was unclear... sorry for that. The current 'note' table has two
columns: 'TableName' and 'TableId', where the 'TableName' field is
an abbreviation for the name of the table the note is related to,
and the 'TableId' is the record within that table.
So, it is in fact a multi-column join between the note table and
several other tables, each with their own 'TableName'.
Steve
On 1/10/2011 1:07 PM, David Ihnen wrote:
A relational database cannot easy express foreign key
constraints to multiple tables from a single column. In fact
I am sure you are using the artifact of identical underlying
native types to overload a single column to contain multiple
different types of data. That is to say that a reference to a
transaction entry is not the same as a reference to a customer
service event. But here you have placed both types of data in
the same column, permitted only because their underlying data
types were identical and you are not making the db check
foreign constraints. Now you want to express this sloppy
schema in the class structure and run into the fact it is
going to be sloppy there too.
You really should fix the schema to be tight and checked but
this is not an ideal world.
There isn't anything stopping you from adding multiple belongs
to directives to your notes result class other than it implies
that all the notes are related to all the tables -
semantically absurd but as long as you use the proper
Re: [Dbix-class] Relationship question/database structure
On Mon, Jan 10, 2011 at 13:54, David Ihnen davidih...@gmail.com wrote: Yes. That was what i was typing basically when Mr Kinyon's message came through. Quite right, sir, this is the sort of approach I would take as well. Each column only has one type of data in it. There are more tables for cross reference, but thats the hit you take for not putting multiple key columns in the notes table or as an alternative creating separate notes tables for each type. Umm ... kinda. This isn't a hit. It's the actual proper model of your data. There is an order and there is a line_item and each of them could have a note. In addition, a single note could be associated with both an order AND a line_item or with 3 orders and 2 line_items. This is considered a feature. Furthermore, by abstracting out the notes concept, you allow notes to have additional things, like attachments. Now, magically, all things that can have notes can also have uploaded attachments. Or users associated with them. Or dates. Or whatever else you have your notes work with. Many-to-many associations (they aren't relationships, properly) are an excellent way of making your database dance, sing, and make perfect julienne fries. I don't know why people freak out when they see them. Rob ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Relationship question/database structure
Agreed, You are a better expresser than I, apologies for clutzing ther ideas. And I do like my julienned fries. David On Mon, Jan 10, 2011 at 2:10 PM, Rob Kinyon rob.kin...@gmail.com wrote: On Mon, Jan 10, 2011 at 13:54, David Ihnen davidih...@gmail.com wrote: Yes. That was what i was typing basically when Mr Kinyon's message came through. Quite right, sir, this is the sort of approach I would take as well. Each column only has one type of data in it. There are more tables for cross reference, but thats the hit you take for not putting multiple key columns in the notes table or as an alternative creating separate notes tables for each type. Umm ... kinda. This isn't a hit. It's the actual proper model of your data. There is an order and there is a line_item and each of them could have a note. In addition, a single note could be associated with both an order AND a line_item or with 3 orders and 2 line_items. This is considered a feature. Furthermore, by abstracting out the notes concept, you allow notes to have additional things, like attachments. Now, magically, all things that can have notes can also have uploaded attachments. Or users associated with them. Or dates. Or whatever else you have your notes work with. Many-to-many associations (they aren't relationships, properly) are an excellent way of making your database dance, sing, and make perfect julienne fries. I don't know why people freak out when they see them. Rob ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk -- David Ihnen Voice contact (562) 743-1807 ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Relationship question/database structure
At the risk of exposing myself to ridicule... I have such a similar Note table in our schema. It's not my fault, I inherited it. You can munge DBIx::Class to follow such a non-relational link. Of course you cannot autogenerate the rels with DBIx::Class::Schema::Loader, David wrote But relationships don't join on multiple columns. Yeah, I somehow missed this in the docs, but you can kludge it by defining a method in your ResultSource/ResultSet classes. We handle the DB integrity via triggers (Oracle) I don't recommend this. I campaign yearly to eradicate this from our database (we actually 2 or 3 non-relational bits like this), but as of yet I have been unsuccessful. Ben On Jan 10, 2011, at 10:18 AM, Steve wrote: I was unclear... sorry for that. The current 'note' table has two columns: 'TableName' and 'TableId', where the 'TableName' field is an abbreviation for the name of the table the note is related to, and the 'TableId' is the record within that table. So, it is in fact a multi-column join between the note table and several other tables, each with their own 'TableName'. Steve On 1/10/2011 1:07 PM, David Ihnen wrote: A relational database cannot easy express foreign key constraints to multiple tables from a single column. In fact I am sure you are using the artifact of identical underlying native types to overload a single column to contain multiple different types of data. That is to say that a reference to a transaction entry is not the same as a reference to a customer service event. But here you have placed both types of data in the same column, permitted only because their underlying data types were identical and you are not making the db check foreign constraints. Now you want to express this sloppy schema in the class structure and run into the fact it is going to be sloppy there too. You really should fix the schema to be tight and checked but this is not an ideal world. There isn't anything stopping you from adding multiple belongs to directives to your notes result class other than it implies that all the notes are related to all the tables - semantically absurd but as long as you use the proper constraints when joining the result will be what you want. It's just... sloppy. No offense intended and I do understand you are working under constraints I am not familiar with that may limit your solutions. Trying to be helpful, Daivd On Mon, Jan 10, 2011 at 12:58 PM, Skylos sky...@gmail.com mailto:sky...@gmail.com wrote: A relational database cannot easy express foreign key constraints to multiple tables from a single column. In fact I am sure you are using the artifact of identical underlying native types to overload a single column to contain multiple different types of data. That is to say that a reference to a transaction entry is not the same as a reference to a customer service event. But here you have placed both types of data in the same column, permitted only because their underlying data types were identical and you are not making the db check foreign constraints. Now you want to express this sloppy schema in the class structure and run into the fact it is going to be sloppy there too. You really should fix the schema to be tight and checked but this is not an ideal world. There isn't anything stopping you from adding multiple belongs to directives to your notes result class other than it implies that all the notes are related to all the tables - semantically absurd but as long as you use the proper constraints when joining the result will be what you want. It's just... sloppy. No offense intended and I do understand you are working under constraints I am not familiar with that may limit your solutions. Trying to be helpful, Skylos On Jan 10, 2011 11:59 AM, Steve st...@matsch.com mailto:st...@matsch.com wrote: Hi All, I'm trying to use an existing schema wherein I have a generic table called 'Note' containing - you guessed it, notes!. The thing is that these notes may be associated with many other types of data and therefore tables in my database. They may be related to a request, user, device, etc. I am sure that this is a fairly common use case, but don't see much in the docs or on the list. The question is two-fold: What's the best structure, and how do we go about defining the DBIC rels? Thanks, Steve ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class http://irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk -- David Ihnen Voice contact (562) 743-1807
Re: [Dbix-class] Relationship question/database structure
Am 10.01.2011 um 17:59 schrieb Steve:
Hi All,
I'm trying to use an existing schema wherein I have a generic table called
'Note' containing - you guessed it, notes!. The thing is that these notes
may be associated with many other types of data and therefore tables in my
database. They may be related to a request, user, device, etc. I am sure
that this is a fairly common use case, but don't see much in the docs or on
the list.
The question is two-fold: What's the best structure, and how do we go about
defining the DBIC rels?
Thanks,
Steve
I'm using something similar to log status changes.
I have many tables with a column akt_status of type varchar
and a table allstatus with columns
status, timestamp, obj_id, obj_name,
where obj_name is the name of the table and obj_id the row in that table
and wherein I can log my whole status change history (done with triggers).
(Of course I cannot define a foreign key constraint in allstatus!)
So for instance from my table pay_order I define
__PACKAGE__-has_many ( status = 'MySchema::DB::Allstatus', 'obj_id', {
where = { obj_name = 'pay_order'} });
Thats it, just add the table info in the attr part of has_many.
I never need a relation the other way , from allstatus to one of the status
logging tables,
there is no use case for that.
Regards
Rolf Schaufelberger
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
