Question Use of One-To-One Table Relationships

[Pete (BSC)]

I am trying to put together an argument for creating a one-to-one table relationship.

Currently we have a meta data table that we store a status. The status along with other fields belonging to the status (time stamp, user) are the most often updated columns after the record has been created.

What I think we need to remove the status fields from the meta data table and create a status table.

We wrote an object that handles updating of the status so that all the business rules for updating the status are in one place.

Currently, a user can have a record displayed and then change the status and to prevent a concurrency exception we refresh the dataset after the record has been updated by the status object (due to our status object doing the update to the record). This goes away b/c we no longer would need to refresh the dataset when the status changed b/c it is stored in a different table.

The hardest thing to overcome is, searching for items with a certain status is very easy b/c its in one table. If we split it... we either need to create a VIEW or sql with a JOIN.

Any thoughts, comments are appreciated.

Thanks,
Pete

 

[cjard]

Here's a simple notion:

If you want to creagte a 1:1 relationship between 2 tables then basically, the data in those tables should be in the same table

Here are 3 tables that are all 1:1

PersonID, Name
PersonID, Age
PersonID, Height


er..

wouldnt it be so much easier if it was:

PersonID, Name, Age, Height

Yep


Maybe i misunderstood your point, but the only time you might choose a 1:1 relationship is if you want to reuse a table
Suppose I have a system where it holds Customers, Suppliers, Contractors and all these types of entity have an Address.. I Might make one address table and then all other tables that represent entities can use it

ContactorID, ServiceType, AddressID
SupplierID, CatalogueName, Speciality, CreditLimit, OnStop, AddressID
CustomerID, LastOrderDate, TotalSpend, CreditLimit, LoyaltyLevel, AddressID

AddressID, House, Street, ZipCode


I rarely do this.. but it does happen and any table that is 1:1 related exhibits what is called key preservation.. everything that was a key before is a key of the join. You can make a join of a key preserved table set and it is updateable:

CREATE OR REPLACE VIEW ContractorDetails AS SELECT * FROM Contractor INNER JOIN Address USING(addressID)

UPDATE ContractorDetails SET ServiceType = 'Path Cleaning', ZipCode = '123456' WHERE ContractorID = 'abc'

 

[Pete (BSC)]

Thanks

CJ thanks... you always put things into a perspective that I can learn from!

The scenario where you may use a 1:1 makes perfect sense; but, in my case... we are not; therefore, we will stick with 1 table.

Pete