Need another Opinion (Database Design)

I need some other opinions on whether or not this is considered a proper database design structure.

Here is the relationship...We have PEOPLE, that each can belong to a COMPANY.

PERSON_TABLE
Person_ID
Company_ID

COMPANY_TABLE
Company_ID

Then each person can trust other people of other companies, but can only trust 1 person per company.

My question is this. In order to maintain a constraint of 1 person per company, is it considered OK to add a the redundant column Company_ID to the PERSON_TRUSTED_TABLE(and then creating a composite primary key on the Person_ID/Company_ID) instead of just adding a trigger to the PERSON_TRUSTED_TABLE to uphold the constraint.

PERSON_TRUSTED_TABLE
Person_ID
Trusted_Company_ID
Trusted_Person_ID

I would appreciate anyone's opinion. Thanks so much!

There is a third option although it's not much different from your PK idea. I think the major questions is in how many other tables do you reference the person_truested_table? You need a PK in your table anyway, so if you don't create a composite PK out of (person_id, trusted_company_id), what would you do to create a PK?

1.) would you create a PK column?

In this case you could create a unique key on (person_id, trusted_company_id), which has the same effect as the PK and you don't need a trigger. This would be my personal preference if you reference this table by many others, simply because you have a 1 column PK.

2.) would you create the PK out of (person_id, truested_person_id)?

Well this in not much different from the (person_id, trusted_company_id) idea, so i would add the trusted_company_id column and create the PK on those as you suggested.

What i would not do at all for this problem is to create a trigger.|||

As I understand your issue, it seems that it is necessary to allow all any and all [Person] to trust any and all [Company] -constrained that any one person can ONLY trust one person from any one company.

If that seems about right, then a constraining composite key (PK or UI) in the [Person_Trusted] table is what is needed. That composite key 'should' be on [Person_ID] and [Trusted_Company_ID].

One person, Bill, ID: 25, is allowed to trust only one person for company Acme Industries, ID: 1001. It doens't matter which Acme Industries employee Bill has trusted, once he has a trust relationship with Acme Industires another cannot be allowed unless he first removes the existing relationship. Or he can just UPDATE the [Person_Trusted] table to reflect the replacement 'trusted' person.

As a side note: You don't need the archaic denotation of [_TABLE] on the table names. It will be obvious from the context that [Person], or [Company], or whatever is in fact, a table. And it is wasted keystrokes to have to continually type something that does not provide any value.

|||I understand what you are saying about the _TABLE thing. I just put it in the post to be more descriptive.

I get your point, but do you realize that you can get to the Trusted_Company_ID from the Trusted_Person_ID. So really, by putting the Trusted_Company_ID on the PERSON_TRUSTED_TABLE, it would be redundant data.

Instead of putting the Trusted_Company_ID on the PERSON_TRUSTED_TABLE, you could just create a trigger on that table that gets the Company_ID from the Trusted_Person_ID, and upholds the constraint. Therefore no redundant data.

After this explanation, do you still think that adding the redundant column Trusted_Company_ID to the PERSON_TRUSTED_TABLE is the correct implementation?

Thank you so much for the input. It is very helpful.|||

In my opinion, this is one of those worthy exceptions to 'normalized' data.

Yes, it is a 'little' redundent. However, anytime you manage the Trusted_Person, you will most likely have the Trusted_Company information at hand -so it is not a 'big deal'. Consider establishing [CASCADE UPDATES] and/or [CASCADE DELETES] as appropriate.

My consideration is that using a TRIGGER is a relatively heavy handed approach, adding significant effort to the server to manage what should be a 'simple' CONSTRAINT.

The effect you are seeking is that for any one Person there can be only one Company trusted (it doens't matter who the Trusted_Person is for that Company). Having to traverse through the Trusted_Person-Company to determine if there is already a record for that Person-Company could be unnecessary effort.

Need a one to one relationship

Can anyone tell me how I can create a one to one relationship from the
following ddl? I feel like I'm close to having this working, but can't
quite get it. I think I'm not sure what I should do with the
PurchaseOrderItem.BuildID. The business rule that I am trying to infoce
is one DistributorNumber per Build as well as one DistributorNumber per
PurchaseOrder while maintaining a one to one relationship betweetn the
row in the BuildItem and PurchaseOrderItem table.
CREATE TABLE [BuildItem] (
[BuildID] int NOT NULL,
[DistributorNumber] varchar(30) DEFAULT ('') NOT NULL,
[DistributorID] int NOT NULL,
[Quantity] tinyint DEFAULT (0) NOT NULL,
[UnitGrams] decimal(7,2) DEFAULT (0) NOT NULL,
[UnitCostPrice] smallmoney DEFAULT (0) NOT NULL,
[UnitSalePrice] smallmoney DEFAULT (0) NOT NULL
)
GO
ALTER TABLE [BuildItem] ADD CONSTRAINT [PK_BuildItem]
PRIMARY KEY CLUSTERED ([BuildID], [DistributorNumber])
GO
ALTER TABLE [BuildItem] ADD CONSTRAINT [FK_BuildItem_Build]
FOREIGN KEY ([BuildID]) REFERENCES [Build] ([BuildID])
GO
CREATE TABLE [PurchaseOrderItem] (
[PurchaseOrderID] int NOT NULL,
[DistributorNumber] varchar(30) NOT NULL,
[BuildID] int,
[ItemDescription] varchar(100) DEFAULT ('') NOT NULL,
[QuantityOrdered] tinyint DEFAULT (0) NOT NULL,
[QuantityReceived] tinyint DEFAULT (0) NOT NULL,
[QuantityBackOrdered] tinyint DEFAULT (0) NOT NULL,
[UnitCost] smallmoney DEFAULT (0) NOT NULL,
)
GO
ALTER TABLE [PurchaseOrderItem] ADD CONSTRAINT [PK_PurchaseOrderItem]
PRIMARY KEY ([PurchaseOrderID], [DistributorNumber])
GO
ALTER TABLE [PurchaseOrderItem] ADD CONSTRAINT
[FK_PurchaseOrderItem_BuildItem]
FOREIGN KEY ([BuildID], [DistributorNumber]) REFERENCES [BuildItem]
([BuildID], [DistributorNumber])
GO
Regards,
Aaron1-to-1 relationships sometimes share the same PK, except that in the
"other" table it's a PK and FK
1:1 Customer to Address
Create Table Customer (
CustomerID INT IDENTITY,
CustomerName NVARCHAR(30)
PRIMARY KEY CLUSTERED CustomerID)
Create Table Address (
CustomerID INT REFERENCES Customer,
Address NVARCHAR(30)
PRIMARY KEY CustomerID )
The other way to enforce the 1:1 is to put a unique index on the "other"
table. In the last example, let's say you wanted the PK to be an
AddressID because you thought the requirements might change in the
future to allow more than one address. You could "temporarily" enforce
the 1:1 using:
Create Table Address (
AddressID INT IDENTITY,
CustomerID INT REFERENCES Customer,
Address NVARCHAR(30)
PRIMARY KEY AddressID )
Create Unique Clustered Index Address_IDX on dbo.Address(CustomerID)
David Gugick
Imceda Software
www.imceda.com

NB: query troubles

I'm making a movie site.

I have 3 tables: actors, movies, mov_act
mov_act is the link tables bacause of the many-to-many relationship.

i need to find the actors that aren't in a specified movie(id=2 for instance).

simple for you guys, hard for a newbie.

any help?

thanks.

btw: not subqueries usage please, because can't upgrade my isp's SQL version ;-)ah yes, good old mysql, no subqueries before version 4.1

select actors.name
from actors
left outer
join mov_act
on actors.id = mov_act.actorid
left outer
join movies
on mov_act.movieid = movies.id
and movies.name = 'ben hur'
group
by actors.name
having count(movies.name) = 0|||thanks you this worked

select mvs_actors.fname
from mvs_actors
left outer
join mvs_mov_act
on mvs_actors.id = mvs_mov_act.act_id
left outer
join mvs_movies
on mvs_mov_act.mov_id = mvs_movies.id
and mvs_movies.id = 1
group
by mvs_actors.fname
having count(mvs_movies.id) = 0

i now have all actors that aren't in movie with id=1

stupid question, but how do i get the once that are in that movie with id=1??

thank you very much|||how do i get the ones that are in that movie with id=1??

easy -- change the left outer joins to inner, and drop the GROUP BY and HAVING clauses

:cool:|||by the way, if you already (somehow) know what the id of the desired movie is, there's no need to join from the mvs_mov_act to the mvs_movies table

actors in the movie id=1 --

select mvs_actors.fname
from mvs_actors
inner
join mvs_mov_act
on mvs_actors.id = mvs_mov_act.act_id
and mvs_mov_act.mov_id = 1|||Thanks very much.

These are the two Queries that work.
The first one seems a bit long(in comparison to the second), but it works ;)

$query = "select mvs_actors.fname,mvs_actors.lname,mvs_actors.id ";
$query .= "from mvs_actors ";
$query .= "left outer ";
$query .= "join mvs_mov_act ";
$query .= "on mvs_actors.id = mvs_mov_act.act_id ";
$query .= "left outer ";
$query .= "join mvs_movies ";
$query .= "on mvs_mov_act.mov_id = mvs_movies.id ";
$query .= "and mvs_movies.id = ".$recordID." ";
$query .= "group by mvs_actors.fname ";
$query .= "having count(mvs_movies.id) = 0 ";

$query = "select mvs_actors.fname,mvs_actors.lname,mvs_actors.id ";
$query .= "from mvs_actors ";
$query .= "inner ";
$query .= "join mvs_mov_act ";
$query .= "on mvs_actors.id = mvs_mov_act.act_id ";
$query .= "and mvs_mov_act.mov_id = ".$recordID." ";