searching from and to location

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

searching from and to location

Author

Topic

averhe
Starting Member

3 Posts

Posted - 2007-11-07 : 09:54:29

Hi all,

I hope someone can give me some tips on how to implement a nice solution.

I need a table to store distances between 2 locations.

First thought was to make a table like this
- id
- fromid
- toid
- distance

However distances can be used in both directions.
So a search to get distance between xx and yy would be: select distance where (fromid = xx and toid = yy) or (fromid=yy and toid=xx)

A unique index (fromid, toid) would make sure no double entries are made, but this still doesn't deny anyone from changing from and to and entering a distance in the other direction (from yy to xx)

Any suggestions for a cleaner db design ?
Ideally one where searches don't need the or, or where the db design can restrict people from entering a distance in the other direction ?
I could force something in business logic to sort locations alphabetically but that also seems like a dirty trick..

Aeneas.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-07 : 10:00:47

is there a rule for which locations can be used for starting locations and which ones can be used for stopping locations?

averhe
Starting Member

3 Posts

Posted - 2007-11-07 : 10:14:19

no, any location can be from or to..

therefor I thought of sorting them alphabetically, making the first always from and the other to..

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-07 : 10:28:00

if you are in control of which location is used for which then that you can force the lower ID to be the "from" and the higher to be the "to", add a unique constraint across the two and finally add a table check constraint that requires the "from" ID to be less than the "to" ID...sorry if this is a rehash of you alphabetic idea

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 10:40:44

Create a trigger like similar to this

CREATE TRIGGER trgMap ON Table1
FOR UPDATE, INSERT
AS

IF EXISTS (SELECT * FROM inserted AS i INNER JOIN Table1 AS t1 ON t1.FromID = i.ToID AND t1.ToID = i.FromID)
	BEGIN
		ROLLBACK TRAN
		RAISERROR('Opposite direction already exists.', 16, 1)
	END

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources