[IrPr]

Originally Posted by AndrewD

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!

You rock Andrew
Yeah, got ya
Special Thanks to you Andrew,