MySQL linking two tables without third table holding the relationship

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

Answer 1

I'm not sure I understand you right but if you want a one-to-many relationship from table_a to table_b you can just add a foreign key f1 from table_a to table_b.

Answer 2

now say table_a had a one to many relationship with table_b

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

No, you wouldn't normally have a third table for a one-to-many relationship.

See the MySQL documentation which explains a one-to-many relationship very clearly.

Answer 3

I think you'll miss out on a couple of things using the method you propose. For example if you want to delete something in table_b, cascading will have to be done manually in table_a, less readable (since it's non-standard) and if you want to find all rows in table_a with a relation to a table_b row it will be slow since you can't have any indices and will have to go through all of the rows in table_a to be sure you found them all.

Answer 4

One-to-many you enforce with a foreign key relationship. Using MySQL, use InnoDB to do this.

Many-to-many you handle with a table in the middle. It's how it's done.

Now, you've got a choice - you can have a crack at re-inventing the wheel (no, don't!) or you can make the most of the fact that some very clever people worked out database theory (read up normalization for the skinny) and then, and this is real important, all the other clever folks who've gone ahead and built relational databases (like MySQL, PostgreSQL, SQL Server, Access, Oracle, etc etc) based everything they've done on all that database theory. Wrinkle here, variations there, but it's all pretty much based on the same stuff.

So, if you're going to design and build databases, do spend some time reading up on the theory behind them, and get it clear in your head, as then you'll be able to work with the databases, and not despite the database!

Does that make sense? I'm not trying to sound snarky, this was advice I was given, and it worked for me!