Hi David
I would add a new autoincrement column and set it as the PK, then as dominic
suggests add a unique contraint that groups together any fields that must be
unique in the table as a set (that might be PersonID, PositionID, StartDate,
FinishDate)
Cheers, Chris
On 23/04/2008, David Phipps <[EMAIL PROTECTED]> wrote:
>
> Thanks Dominic, that sounds like a better plan as I will be needing this
> table for other modules in a later phase . 2 questions:
>
> If I have the following columns in the tjoinpeopleposition table:
> PersonID,
> PositionID,CommitteeMemberTypeID,ClergyStatusID,StartDate,FinishDate,Year,OtherPosts.
> Which one(s) do I set as the Primary Key so that Reactor can load and save a
> record? I can't seem to get my head around how reactor will distinguish
> between 2 (or more) records that are virtually identical apart from a
> StartDate or FinishDate.
>
> How do I go about overriding the default load method?
> Cheers,
>
> Dave
>
> On 23 Apr 2008, at 13:57, Dominic Watson wrote:
>
> Hi David, I don't think you need a fourth table here. While what you
> suggest is logical, the current logic is also correct - a unique person-
> position row is defined by the person, the position and the date they
> occupied the role. I would suggest adding a primary key and making sure
> there is a unique constraint / index on personId, roleId and dateRange.
>
> If you need to, you can still query a person for unique roles:
>
> SELECT DISTINCT PersonId, RoleId FROM 'tjoinpeopleposition'...
>
> Perhaps this is a situation where you'd want to override the default load
> method for the record object and put in custom SQL (rather than changing the
> db model to fit in with Reactor).
>
> HTH
>
> Dominic
>
>
> On 23/04/2008, David Phipps <[EMAIL PROTECTED]> wrote:
> >
> > Thanks Chris,
> >
> > I thought about adding a PK to the table but that still doesn't get away
> > from the fact that there could be 2 or more rows matching the personid and
> > positionid. It seems that this table has been used to keep a history of all
> > the times a person has been in a certain position. So if a person was in a
> > position from 2005-2006 and then again in 2007-2009 then there are 2 records
> > in the join table - which one should reactor update?? It's ambiguous and
> > reactor rightly reports that.
> >
> >
> > I think what I need to do is create another join table which uniquely
> > joins a person to a position and then use the original tjoinpeopleposition
> > table as a historic record which is added to each time a save is made.
> >
> >
> > Sound about right??
> >
> >
> > Cheers,
> >
> >
> > Dave
> >
> > On 23 Apr 2008, at 12:18, Chris Blackwell wrote:
> >
> > You could do
> >
> > myRecord = reactor.createRecord('tjoinpeopleposition').load(PersonId=a,
> > PositionId=b, StartDate=c, EndDate=d, etc...)
> >
> > But i'd be more inclined to look at adding a primary key to the table
> >
> > Chris
> >
> >
> >
> > On 23/04/2008, David Phipps <[EMAIL PROTECTED]> wrote:
> > >
> > > Hi,
> > >
> > > Apols for the vague subject line. I am in the process of converting an
> > > Access based system to a Web based, MySQL system. I am using Reactor to
> > > manage the database. There are various tables,join tables and lookup
> > > tables.
> > >
> > > When I converted the db from Access to MySQL I assumed (more fool me)
> > > that the join tables were just linking tables. So for the following
> > > tables:
> > > tbl_people,tbl_position there is a tjoinpeopleposition table to join them
> > > together as a person can hold many positions and a position can be linked
> > > to
> > > many people.
> > >
> > > It appears that the tjoinpeopleposition table has been used for more
> > > than just a linking table and holds extra data and just to make it more
> > > tricky there may be multiple rows with the same personid and positionid (I
> > > wanted to use these as the primary key so I could create a record object).
> > > Which means that I can't get a single link between a Person and a
> > > Position.
> > >
> > > The rows where the PersonID and PositionID is the same does have other
> > > data which is different (Start Date, Finish Date, some other ids from
> > > other
> > > tables).
> > >
> > > Is there a neat way in Reactor to get a single record that matches
> > > several criteria. Or do I need to use a custom gateway query to get the
> > > data
> > > I need?
> > >
> > > For example, if a person is linked to the same position twice, one
> > > record will have a FinishDate and can therefore be ignored.
> > >
> > > And if so do I need to do this manually. As in use a gateway query to
> > > get the record based upon the extended criteria and then also save the
> > > data
> > > in the same way rather than trying to createRecord and then save()?
> > >
> > > Sorry if this is not clear, my brain is a bit frazzled - you've gotta
> > > love Access!!
> > >
> > > Cheers,
> > >
> > > Dave
> > >
> > > _______________________________________________________________________
> > > David Phipps
> > > Director, Chapel Studios
> > >
> > > T +44 (0)20 7100 6980 F +44 (0)20 7100 6981 M +44 (0)7765 240899
> > > New Broad Street House, 35 New Broad Street, London, EC2M 1NH, UK
> > >
> > > http://www.chapel-studios.co.uk
> > >
> > > _______________________________________________________________________
> > >
> > > The Chapel Studios group of companies are registered in England.
> > > 'Chapel Studios' and the Chapel Studios logo are registered trademarks of
> > > Chapel Studios. The information in this email is confidential, intended
> > > solely for the addressee, and may be legally privileged. If you are not
> > > the
> > > addressee or authorized to receive this for the addressee, you must not
> > > use,
> > > copy, disclose or take any action based upon this message or any
> > > information
> > > herein. If you have received this message in error, please advise the
> > > sender immediately by reply e-mail.
> > >
> > >
> > >
> > > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> > > -- -- -- --
> > > Reactor for ColdFusion Mailing List
> > > [EMAIL PROTECTED]
> > > Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
> > > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> > > -- -- -- --
> > >
> > >
> > >
> >
> > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> > -- -- --
> > Reactor for ColdFusion Mailing List
> > [EMAIL PROTECTED]
> > Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
> > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> > -- -- --
> >
> >
> >
> > _______________________________________________________________________
> > David Phipps
> > Director, Chapel Studios
> >
> >
> > T +44 (0)20 7100 6980 F +44 (0)20 7100 6981 M +44 (0)7765 240899
> > New Broad Street House, 35 New Broad Street, London, EC2M 1NH, UK
> >
> >
> > http://www.chapel-studios.co.uk
> > _______________________________________________________________________
> >
> >
> > The Chapel Studios group of companies are registered in England.
> > 'Chapel Studios' and the Chapel Studios logo are registered trademarks of
> > Chapel Studios. The information in this email is confidential, intended
> > solely for the addressee, and may be legally privileged. If you are not the
> > addressee or authorized to receive this for the addressee, you must not use,
> > copy, disclose or take any action based upon this message or any information
> > herein. If you have received this message in error, please advise the
> > sender immediately by reply e-mail.
> >
> >
> >
> > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> > -- -- --
> > Reactor for ColdFusion Mailing List
> > [EMAIL PROTECTED]
> > Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
> > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> > -- -- --
> >
>
>
>
> --
> Blog it up: http://fusion.dominicwatson.co.uk
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> -- --
> Reactor for ColdFusion Mailing List
> [EMAIL PROTECTED]
> Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> -- --
>
>
> _______________________________________________________________________
> David Phipps
> Director, Chapel Studios
>
> T +44 (0)20 7100 6980 F +44 (0)20 7100 6981 M +44 (0)7765 240899
> New Broad Street House, 35 New Broad Street, London, EC2M 1NH, UK
>
> http://www.chapel-studios.co.uk
> _______________________________________________________________________
>
> The Chapel Studios group of companies are registered in England. 'Chapel
> Studios' and the Chapel Studios logo are registered trademarks of Chapel
> Studios. The information in this email is confidential, intended solely for
> the addressee, and may be legally privileged. If you are not the addressee
> or authorized to receive this for the addressee, you must not use, copy,
> disclose or take any action based upon this message or any information
> herein. If you have received this message in error, please advise the
> sender immediately by reply e-mail.
>
>
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> -- --
> Reactor for ColdFusion Mailing List
> [EMAIL PROTECTED]
> Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> -- --
>
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Reactor for ColdFusion Mailing List
[EMAIL PROTECTED]
Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
