Finding association using BRIDGE table

View previous topic View next topic Go down

Finding association using BRIDGE table

Post  rhasnat on Mon Jun 17, 2013 10:42 pm

Finding association using BRIDGE table
Hi all , just wanted to ask if there is any efficient way of querying the bridge table to find associations i.e. which two/more car options are sold together very often or if a patient has dignosis 'A' then he/she also have diagnosis 'B' quite often . So my query result should show diagnosis A and B occured together say 20 times in a particular dataset or people bought option1 ,2 and 3 together 30 times when purchasing a car .

Any thoughts ? also, if bridge table is avoided then what is the alternate for this ?

bridge table fields : Option_group key , optio_ID etc.

rhasnat

Posts : 1
Join date : 2013-06-17

View user profile

Back to top Go down

Re: Finding association using BRIDGE table

Post  zip159 on Mon Jun 24, 2013 7:11 pm

You wouldn't easily be able to report directly off the bridge table.  Instead you would want to create a separate fact table that holds the data on which associations are seen together.  This is market basket analysis problem which typically require a bit of processing and storage.

Take a look at http://rolandholtermann.wordpress.com/2012/05/06/market-basket-analysis-with-sql/ for an example with sales.  You can also read about it in The Data Warehouse Tookit.

zip159

Posts : 6
Join date : 2013-06-24

View user profile

Back to top Go down

Re: Finding association using BRIDGE table

Post  VHF on Wed Jul 10, 2013 3:38 pm

For multiple choice scenarios (car options, patient diagnosis, etc.) I like to build a string of unique identifiers, which makes it easy to search combinations of options and/or exclude others.  If the number of options is very small (<26), then letters work well.  Otherwise a delimited string of numeric identifiers.  Examples: ABDMQ or |12301|14705|18234|29331| (make sure to include the leading and trailing delimiters!)

For example, to find cars that had option A and option B but not option D the SQL would look like this:
WHERE Options LIKE '%A%' AND Options LIKE '%B%' AND NOT Options LIKE '%D%'

To find patients with either of two diagnosis, it could look like this:
WHERE Diagnosis LIKE '%|13475|%' OR  Diagnosis LIKE '%|17455|%'

When using this approach, it is usualy in addition to a bridge table to represent the many-to-many relationship--some queries are bettered handled with pattern matching, and some relationaly using the bridge table.  But for finding when a particular option/diagnosis was not present the pattern matching approach is generally much easier.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Finding association using BRIDGE table

Post  sachij3u on Thu Jul 18, 2013 4:03 pm

If there are strong business need to use this kind of query then you might want to create fact table purely to answer these query needs. Else bridge table is an excellent solution to resolve the patient or Vehicle Options (M-M relationship).
avatar
sachij3u

Posts : 19
Join date : 2013-07-11
Age : 36
Location : Herndon, VA

View user profile

Back to top Go down

Re: Finding association using BRIDGE table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum