Re: Table Structure - Peter - com.mysql.lists.win32

10 messages in com.mysql.lists.win32Re: Table Structure

From	Sent On	Attachments
Corey Tisdale	08 Nov 2001 15:33
Daniel Lucazeau	08 Nov 2001 16:08
DL Neil	08 Nov 2001 16:37
Peter	08 Nov 2001 17:04
Jonathan Ball (acsjob)	09 Nov 2001 02:40
Peter	09 Nov 2001 03:10
DL Neil	09 Nov 2001 04:21
Corey Tisdale	09 Nov 2001 15:02
DL Neil	09 Nov 2001 18:43
DL Neil	10 Nov 2001 02:38

Subject:	Re: Table Structure
From:	Peter (pw...@post4.tele.dk)
Date:	11/09/2001 03:10:06 AM
List:	com.mysql.lists.win32

Hi,

When I have many to many relations, I preferer to use ...

SELECT subcat.name, subcat.cat_id WHERE (subcat.cat_id LIKE '%4%') AND (subcat.cat_id LIKE '%5%') ORDER BY subcat.cat_id ASC

... and reduce the programming and controls keeping additional tables updated - if these updates fails, you are lost specially having many records.

In this way You only need one datafield for having all the "codes" toge´ther, and if further "codes" comes up this can bee added into the same datafield, instead on re-work on the tabelstructure.

Remember also to consider making the right index for your keys.

----- Original Message ----- From: "Jonathan Ball (acsjob)" <j.b...@rgu.ac.uk> To: <cor...@bbqguys.com>; <win...@lists.mysql.com> Sent: Friday, November 09, 2001 11:41 AM Subject: RE: Table Structure

Corey,

Neil and Peter have both given good answers for the one to many relationship. Daniel is correct about needing a third table if you want a many to many relationship but this is not necessary for a one to many relationship. The reason you need a third table in the many to many relationship is because it is effectively 2 one to many relationships back to back so to speak.

If you want some web references to SQL have a look at:

http://sqlcourse.com/ an interactive SQL tutor http://www.arsdigita.com/books/sql/ a good site (if a bit quirky) http://www.contrib.andrew.cmu.edu/~shadow/sql.html a general syntax and specification site http://w3.one.net/~jhoffman/sqltut.htm extensive details on SQL syntax http://www.indus-soft.com/ software manufacturers who produce a utility called "WinSQL". You can download WinSQL Lite for free. This is a nice utility that interfaces with almost any database through ODBC (assuming you have the drivers). If you want to relate to your RDBMS databases in a "pure" SQL type way then this is the tool for you. I used it a lot before I found MySQL.

Bear in mind that there are some slight "dialects" within what is broadly known as SQL. Microsoft (and Access) is a good case in point. I hope this helps.