[AndrewD]

Originally Posted by IrPr

Hi Andrew, im just tryin Hack/Modify LDM
just a Q, why u seprated the catid and keywordid from _linkslink table into _linksltok and linksltoc instead if a field inside _linkslink table? whats this kind of relationship? is that standard?

im creating my own artist and track tables but donno how to make its relationship into _linkslink table
i can add fields called artistid and trackid into _linkslinks structure, and also can use ur method, creating another tables named _linksltoa and _linksltot

but which one is better? im newbie in MySQL database designin

Special thanks for this AWESOME Mode, LDM, even u dont reply this post

PS: sorry if my grammar sux:P

The basic question in designing database tables is whether the relationship between an entry and its data is 'one-to-one' or 'one-to-many'. If it's one-to-one (e.g. a category has only one name and always has one name), then you can keep the category name as a column in the same table as the categoryid. However, if it's one-to-many (an entry can be in one or several categories), you can't. Also, ideally, *everything* in a table row should be logically connected and required - a classic example is that a table of professors should not include the courses they teach, because you would lose the ability to deal with new professors and emerti or to efficiently find courses with no professor assigned. (See http://en.wikipedia.org/wiki/Database_normalization if you want the background )

So in LDM the table structure reflects the fundamental data types - a category, an entry, a keyword, a hit, etc, and the 'XtoY' tables allow you join these together - find all the keywords associated with an entry and vice-versa, all the entries associated with a category, etc.

What you are trying to do is add a feature to LDM that people have suggested for some time (and I haven't had time to deal with), i.e. add extra data to the system. There are pros and cons to doing it in different ways:

- If you add one new column to the 'entries' table (links), then the logic is easy to implement but very inflexible. There are ways of forcing several different data items into the one columns (use the php serialize function, for example), but it becomes a mess.
- If you add a new column for each new datatype, it become a mess very quickly.
- If you handle it in a different table, then the logic is much cleaner, but it can become quite tricky to construct efficient SQL queries.

In LDM, the hairiest piece of code are the routine that build the linkbits (the individual rows which show each entry) [get_linklistbit] and the routine that works out the required SQL query [get_mainsql]. get_mainsql constructs the required 'joins' on tables to pull in the required information. Unfortunately, joins can easily get very expensive, and you can end up killing your server if you are not careful.

For your purposes, I think it would be easiest to create new tables which have this form:

1) artistid, artistname, etc

and 2) linkid, artistid

or

1) trackid, trackname, etc

and 2) linkid

Hope that helps!