A True Search Accross 2 Tables

[dosborne]

I have a Music database that contains tables: CDs, Tracks, MP3s

Tracks are related to CDs - MP3's are related to nothing.

I need a way to Perform a Find for a Title or Artist accross both the MP3s and Tracks tables and then display the found count for both.

I tried the global relational method, but it doesn't work like a Find - that method requires the text to be an exact match, sometimes the only thing know is a word a two (Finds show records where only a portion of the field matches).

I thought about combining Tracks and MP3s but the only there would be alot of null values for unrelated records to CDs.

Any ideas??

[Fenton]

I say combine them. This is a good illustration of the problems caused by separating things which are actually pretty much the same thing, but which seem different for other conceptual reasons. They are both basically musical "songs" or named pieces of music, with very much the same information, except for the fact that some are from a CD and some are loose.

Another fact that tells you they should be in the same table is that they are both on the same granular level, in relation to anything else. The null values for a CD ID (and it should be an ID, not the name that ties tracks to CDs) is really a very small problem, if any.

One of the main operations you would do on them, as you say, is to Find for the same data, title and/or artist. Obviously easier and faster if they're in the same table.

[dosborne]

I've sort of known that I need to combine them, I've just have some scripts to re-write for the importing of downloaded info that I'm trying to avoid.

But I will have null values for MP3s in the fields: TrackNumber, CDID

And null values for Tracks in the fields: Location, BitRate

Do you foresee any problem with null values in a table with 25,000 records? Growing only by about 80-100 monthly.

Thanks,

[Fenton]

No, there are usually a few fields that have data for some records and not for others. It is not a problem, unless the two "conceptual" things share almost nothing, AND it's a big problem for the interface. It almost never is really a problem. Beginners especially are tempted to break things into two tables simply because they think it's easier to keep them apart, and don't know how to create the structure to keep them together but treat them differently. But splitting them usually causes more problems than it cures, making Finds and reports difficult.

The only time I see null values interfering is in summary reports. So you'll end up with blank locations at the top of a subsummary area, ahead of locations with values.

You might want to sort portals in CDs by TrackNumber. That's fine, 'cause MP3s won't be in those portals anyway.

25,000, with an additional 1000 a year is not a huge amount of records. It's a small amount of data for each, with few or no calculations. You should use IDs for the CD relationship however and not the name. That'll save a lot of space.

Where is the downloaded info coming from? Amazon?

[dosborne]

CDs and Tracks are linked via an ID not the name.

The downloaded info is coming from two different sources, one is from the CD subscription service I belong to - it's an excel file. The other is from iTunes using an AppleScript I found from Doug Malcolm's Applescripts website.

The database is used for my DJ service, so when I'm out at an event I can search the name of a requested song and quickly find whether I have it or not and if so where it is located. Now that my MP3s collection has grown, it's helps to search across the tracks as well as the MP3's to further the search. That's why I had a feeling that combining them would be a better solution, but there are other issues I'll have to re-work while doing this. The main thing being the CDs location. I have a separate table for CD locations, storing info like: case 1, shelf, binder 2, etc. This table is used to give the CD record a graphical icon to represent whether I have it on location or not. Blue circle = yes, Grey = no, Tracks shares this info using the relationship. Now, I'll have to create a different relationship from Tracks to the locations directly for MP3's, because I have a feeling eventually I will start storing some MP3s on an external hard drive and not at the event.

Having your input as to combining the two tables helps push me to doing what I've been procrastinating.

Thanks,