Help w/ Data Structure

In a simple sales contact management software that I developed, users "own"
companies that are part of their portfolio. This is denoted by a row in the
UserCompany table. I use a join table because multiple users can own the
same company.
CREATE TABLE UserCompany
(
UserId int,
CompanyId int,
TargetCompany bit
)
The TargetCompany column is used to denote whether or not a certain company
is an important company in the specified user's portfolio. We run many
reports which only include a user's target companies.
I realized though, that if I run one of these reports for a date range that
is in the past, the data will always be based on the target companies
currently in UserCompany. i.e. Because of the data structure, I have no way
of telling if a user's company used to be a target company sometime in the
past.
Can someone suggest how I could modify this data structure to be able to
historically track when companies where denoted as target companies for a
certain user?
So, if Company A was denoted as a target company for User 1, between
01/01/2004, and 12/31/2004, but not later, how would I show that?
Thank YouHow about adding columns for StartOfInterestDate and EndOfInterest date,
denoting when the user had an interest in a particular company. You should
then be able to modify your query to include this within your criteria.
Cheers,
James Goodman
"George Durzi" <gdurzi@.hotmail.com> wrote in message
news:OyQAupkUFHA.580@.TK2MSFTNGP15.phx.gbl...
> In a simple sales contact management software that I developed, users
> "own" companies that are part of their portfolio. This is denoted by a row
> in the UserCompany table. I use a join table because multiple users can
> own the same company.
> CREATE TABLE UserCompany
> (
> UserId int,
> CompanyId int,
> TargetCompany bit
> )
> The TargetCompany column is used to denote whether or not a certain
> company is an important company in the specified user's portfolio. We run
> many reports which only include a user's target companies.
> I realized though, that if I run one of these reports for a date range
> that is in the past, the data will always be based on the target companies
> currently in UserCompany. i.e. Because of the data structure, I have no
> way of telling if a user's company used to be a target company sometime in
> the past.
> Can someone suggest how I could modify this data structure to be able to
> historically track when companies where denoted as target companies for a
> certain user?
> So, if Company A was denoted as a target company for User 1, between
> 01/01/2004, and 12/31/2004, but not later, how would I show that?
> Thank You
>|||Nice idea.
Let's say a company is a target company for 1 yr, then not a target company
for some time, and back to being a target company (phew)
Do you think this would be handled best with multiple rows for the same
User/Company?
"James Goodman" <jamesATnorton-associates.co.ukREMOVE> wrote in message
news:%23PhVr5kUFHA.3344@.TK2MSFTNGP10.phx.gbl...
> How about adding columns for StartOfInterestDate and EndOfInterest date,
> denoting when the user had an interest in a particular company. You should
> then be able to modify your query to include this within your criteria.
> --
> Cheers,
> James Goodman
> "George Durzi" <gdurzi@.hotmail.com> wrote in message
> news:OyQAupkUFHA.580@.TK2MSFTNGP15.phx.gbl...
>|||Yes, sounds like you need a separate log table.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"George Durzi" <gdurzi@.hotmail.com> wrote in message
news:uW46R7kUFHA.1148@.tk2msftngp13.phx.gbl...
> Nice idea.
> Let's say a company is a target company for 1 yr, then not a target
> company for some time, and back to being a target company (phew)
> Do you think this would be handled best with multiple rows for the same
> User/Company?
> "James Goodman" <jamesATnorton-associates.co.ukREMOVE> wrote in message
> news:%23PhVr5kUFHA.3344@.TK2MSFTNGP10.phx.gbl...
>|||Thank you both for your help.
George
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:enaeB$kUFHA.3176@.TK2MSFTNGP12.phx.gbl...
> Yes, sounds like you need a separate log table.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "George Durzi" <gdurzi@.hotmail.com> wrote in message
> news:uW46R7kUFHA.1148@.tk2msftngp13.phx.gbl...
>|||CREATE TABLE UserCompanyHistory
(user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON UPDATE CASCADE,
company_id INTEGER NOT NULL
REFERENCES Companies(company_id)
ON UPDATE CASCADE,
company_priority INTEGER NOT NULL
CHECK (company_priority > 0),
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
end_date DATETIME, -- null means current\
CHECK(start_date, end_date),
PRIMARY KEY (user_id, company_id, start_date)) ;
Do not use the BIT datatype; use a prioirty number instead. Time is in
durations, so add start and end times to the table. Now for some
views:
CREATE VIEW UserCompany (user_id, company_id, company_priority)
AS SELECT user_id, company_id, user_id, company_priority
FROM UserCompanyHistory
WHERE end_date IS NULL;
CREATE VIEW UserBestCompany (user_id, company_id)
AS SELECT user_id, company_id, user_id
FROM UserCompanyHistory AS H1
WHERE company_priority = 1;
or if you do not maintain a nice ordering in the priority column:
CREATE VIEW UserBestCompany (user_id, company_id)
AS SELECT user_id, company_id, user_id
FROM UserCompanyHistory AS H1 WHERE company_priority
= (SELECT MIN(company_priority)
FROM UserCompanyHistory AS H2
WHERE H1.user_id = H2.user_id _
You will need some triggers to maintain the history table integrity,
but they are not tricky