Thursday, February 18, 2010

Programmer - MySQL linking two tables without third table holding the relationship

Programmer Question

Hi guys,



say you have two tables



table_a

f1 <- PKEY
f2
f3...

table_b

b1 <- PKEY
b2
b3...


now say table_a had a MANY to MANY relationship with table_b



normally you'd have a third table to hold that relationship



table_c

c1 <- PKEY
b1 <- PKEY of table_b
f1 <- PKEY of table_a


also say b1 + f1 for whatever reason could not be the PKEY of table_c - just for arguments sake.



Now would it be viable/advisable to do the following



in table_a you have field MANY_Bs which holds the many relationship like this:



table_a

f1:1

f2:'xyz data'

MANY_Bs: '1,2,3,4,5'


(thus showing that row 1 in table_a is connected to rows 1-5 of table_b)



then use the following query to select such a relationship



SELECT * FROM table_a, table_b WHERE
FIELD_IN_SET (table_b.b1, table_a.MANY_Bs)


My concern is a) loss of preformance b) loss of normalisation (my brain is a bit fried to work out (b) just right now)



If any MySQL guru can see any problems with such a set up?



Many Thanks

No comments:

Post a Comment

LinkWithin

Related Posts with Thumbnails