How would you handle this reporting problem?

[dmusicant]

I have a table with a bit over 600 records and one of the reports lists records alphabetically on one of the fields. The issue I have here is that roughly a dozen of entries in the field that determines the order of records displayed contain two entities. The report as constituted has those records in accordance with the first of the entities, the other is effectively ignored. I'd like to produce a better report that includes the secondary entity on a separate line. Thus, Shostakovich/Berg would have two lines, one for Shostakovich, the other for Berg.

I'm thinking of adding a character field called SECOND or similar, that has the secondary entity name. I could create a function that executes a SQL select command on the table that pulls records from only those that have non-empty SECOND records and joins those records with the report I have going now.

I think this will work, but wonder if I'm getting unnecessarily complicated or if there's a cleaner, leaner, smarter way to do this. I have the feeling I'm missing something obvious here.

 

[Dan Freeman]

It would help if you could post a CREATE CURSOR statement that shows us the structure you're trying to describe and a couple of INSERT statements that show us what the data actually looks like.

(And a lot of times you'll stumble on your answer in the act of doing this. )

 

[dmusicant]

You want to know the structure of the table? Seems unnecessary, but I could do that. The report is simple enough, it displays some of the fields and it's ordered alphabetically on one of the character fields. The table actually has a fairly complex structure (there are 34 fields), but for the report it doesn't use a lot of the info in the table. I could paste the structure into a post here, but I don't think it will help.

 

[dmusicant]

Here's the SQL statement that the report runs from (it's generated using Foxfire!):

SELECT ALL;
MUSIC.POSITION,;
MUSIC.SONYRIGHT,;
MUSIC.PIONEER,;
music.genre,;
MUSIC.TRACKS,;
MUSIC.MAKENEW,;
MUSIC.ACTUALNUM,;
MUSIC.PIONEER3;
FROM "MUSIC" MUSIC;
INTO CURSOR REPOALPH;
WHERE (MUSIC.ARTIST<>"---";
AND MUSIC.PIONEER BETWEEN 1 AND 600);
ORDER BY 2

** (ORDER BY with field names)...
** ORDER BY SONYRIGHT

 

[Dan Freeman]

Having said that, by the way, this sounds like a classical record collection.

I'm hard pressed to imagine doing that in a single table and still have reporting flexibility. You need several tables. This is just a quick take without putting much thought into it.

[ul]
[li]Composers (name, dates, etc.)[/li]
[li]Works (Title, pointer to composer, copyright date, etc.)[/li]
[li]Albums (Title, Publisher, Publication date, etc.)[/li]
[li]Performers (conductor, soloist, etc.)[/li]
[li]Orchestras ?[/li]
[li]Contents (pointers to Album, Composer, Work, Orchestra, conductor, Performers, etc.)[/li]
[/ul]

(Can you tell I've done music catalogs before? )

This way it's easy to query "All works of Shostakovich" or "All recordings conducted by Georg Solti". In other words, you set the data up so it can answer any question you ask it without resorting to kludge queries.

 

[dmusicant]

Well, it's a record collection but it's all over the place, many many genres. That example was just one record, and hypothetical at that. In fact I have one that I think is Shoenberg/Berg/Krenek.

I would never query orchestras. Actually, the table is in my do-all application and I can search for composer, title, band, group, well, anything that's in the default search field, which I usually set up to have everything in it that I'd want to search for.

This particular table, however, isn't one I generally access from a computer but one I access by grabbing the printed reports, thus this for-me unusual problem. I know I can solve it myself, I thought I'd post here and see if there's a standard way to do this, or a best way. Perhaps it's an unusual type of thing. Thing is, the field that the report is ordered by usually refers to just one significant entity, but in several cases there are two such entities... or three as in Shoenberg/Berg/Krenek.

 

[Dan Freeman]

Yeah, my own music collection spans all genres. And I've never catalogued it because I know the level of work involved in doing it properly. One of my HUGE gripes with iTunes is their under-implemented catalogue. I can never find what I'm looking for.

But back to your vague request: I asked for data. You gave a query. That isn't data. Now you say your question was a hypothetical. I've given you a hypothetical answer.

If you want to give an actual example of data and explain what you want to get from it, someone can give an actual answer.

 

[Olaf Doschke]

You have a search field? Are you talking about the table having one memo you search in with LIKE or $-operator? Or do you mean a search field in a form, like google search, which means you search an entered term in all table fields?

It's a bad idea to have several records data slash-separeted in one field and it's an even worse idea to try to split this up into multiple records during a query. It's a medium good idea to split this up into seveal fields and be able to store two or three interprets, the only straight forward solution is to create a 1:n related table, also for other things, as dan already suggested.

Searching should rather be done with a fulltext index and for that it's better to have data in SQL Server.

Bye, Olaf.

 

[Olaf Doschke]

Actuslly in this case the relations between songs and interprets are as follows:

Interprets - table with interpret names
Songs - table with song titles, referencing main interpret as interpretID
AddtionalSongInterprets - n:m relation table to cover songs with two or more interprets only.

That's what I'd do, because most of the songs have one interpret only, and I'm not talking about cover songs, I mean working together in the same song, eg duetts like Toni Bennett and Amy Winehouse. A cover song is a song record of its own, as it differs in some attributes other than the title anyway, eg release date and the record label.

Bye, Olaf.

 

[dmusicant]

An example of data:

My all-purpose data program, that I wrote and maintain in FoxPro, has a lot of bells and whistles. It can search any field I specify in metadata. Most times that's a memo field, but I can change that to any other field by editing the metadata table. Sometimes I have it a character field, if only temporarily.

In the case of this table, it's searching a memo field. I almost never do this, can't think of another exception, but in the case of this fairly complex table I have created a dedicated field named SEARCH. In the SEARCH field for the record I mentioned above is this:

- - - -
Artist: Berg/Schoenberg/Krenek

Title: Sonata Op. 1 / 3 Piano Pieces Op. 11 / Sonata #3 Op. 92 #4 (GG 7)

- - - - - - - - - - - - - - - -
Upstairs Pioneers: 226 Position: Top - 226


Genre: CLASS

Tracks: 8

Actual CDs: 2
- - - -

The contents of that memo field is constructed by code that runs when I close the browse. Now, my program allows me to search for any of 3 character strings within the search field (that field being specified in metadata, as I said). It can find records with all of the specified strings or any, my choice. I almost always go with all, not any. I can turn on case sensitivity by checking a box. I sometimes specify only one character string, often two, sometimes three.

Searching should rather be done with a fulltext index and for that it's better to have data in SQL Server.

Hmm. I think I probably have some limited-usage free version of SQL Server somewhere. If I run that on my NAS it might help a lot with my often slow response to my queries in this program. I am using the "$" in my searches in FoxPro, all set up in a big PRG. I think a lot of the slowness has to do with my network or rather my Synology NAS. I am contemplating setting up running on local data and keeping that synched with the NAS using batch files that copy the latest version of tables from local machines to and from the NAS. I run on several local machines. This way I can run my searches locally, presumably get pretty quick results. The batch files will run when I take action on a new local machine, a wait I can live with, and after completing usage on a local machine, a batch file will update the NAS with changed tables, a wait even easier to live with.

Most of the tables in this system of mine (and there are a great many, nearly 400 at this time) are there for search purposes, but this particular table I don't often search. It is there in order to keep track of music CDs in high capacity players. I print reports (one alphabetically ordered, the other ordered by slot number in the player). It's the reports that are the thing with this table.

 

[Olaf Doschke]

I understand it this way: Some metadata defines in what field of a table to search and most often that is a memo field having it's content put together from single informations, maybe via textmerge, but really doesn't matter.

This way you can only do an instring search via LIKE or $ operator, your search terms are all (or any) contained in that memo. That way you only have unspecific searches, no artist searches or title searches. I wonder how you would gain from splitting an artist field into artist1/2/3.

This kind of search may work for 10000 record still quite fast, but is not the ideal way even for unspecific fulltext searches. Searching for son you find boths songs by Jackson and about sons oe reason etc.

You can of course scan through result records and then find out more specifically where the match was, eg in artist or title field - if you have that separately, too, as I understand it.

I see no big point in putting together such an overview about a song in a memo, you can always do so on the fly with detaul data. And if you want full text search as said MSSQL has that built in better than could be done with a word index. You may take a look at thread184-1553613, but this structure is inferior to full text search of MSSQL and that even is a feature of Express versions.

Bye, Olaf.

 

[dmusicant]

Most of my tables in this system simply search a metadata-specified memo field that's populated with data I either type in directly or fill with copy/paste. It's only the rare table that has a searched field that's populated from several of the field's table, which is the case with the music CD info table.

 

[Mike Lewis]

I think I probably have some limited-usage free version of SQL Server somewhere. If I run that on my NAS it might help a lot with my often slow response to my queries in this program.

I suggest that's a bad idea. A query in the free version of SQL Server won't necessarily run any faster than the same query in VFP. And the fact that it's run from a NAS isn't a reason to change database.

If your query is slow in VFP, the first thing to do is to find what is slowing it down, and remove it. Moving to SQL Server introduces a whole load of new complications - not to mention a large amount of time - with no guarantee it will solve the problem (remember, VFP and SQL Server use similar query optimisation techniques).

In this case, the obvious reason for any poor performance is probably caused by your use of the $ operator. With this type of substring search, there is no good way to optimise the query. If performance is your main issue, the only solution - and this will apply to any database - will be to restructure your data in a more relational way.

I won't repeat the advice you've already been given. But, in the example you quoted, this clearly has three composers, so you need a one-to-many relationship between works (or recordings, or whatever) and composers. Once you've done that, the query will fall into place.

But: Is performance really you main problem? In your original question, you asked if there was a cleaner, simpler way. There probably is, but it will be a different solution from one concerned with performance alone. And, again, there is no need to the likes of SQL Server in order to find it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I was specifically suggesting full text search in MSSQL. That isn't automatic. First you create full text indexes, then you don't do the same query, but use full text search functione, eg CONTAINS: [URL unfurl="true"]https://technet.microsoft.com/en-us/library/ms187787(v=sql.105).aspx[/url]

It's not simply the same as VFPs $ operator, it is driven and optmized by the full text indexing of the data you migrate into SQL Server.

NAS is no good platform for a SQL Server DB, you need a Windows Server and not a NAS to run it and store its data. NAS typically run a Linux OS and no MSSQL Server runs on them, nor is it a good idea to run the MSSQLSERVER service on a Windows server and store the database mdf/ldf files on a NAS.

Bye, Olaf.

 

[Mike Lewis]

I was specifically suggesting full text search in MSSQL

I missed that (I came to this thread late). But even so, switching to a different back-end does seem a bit drastic - unless the problem is critical and there is absolutely no other solution.

Come to think of it, isn't there a full-text search product for VFP? PhdBase? Or am I way out of date?

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[dmusicant]

My performance issues in this system I have I don't think are mainly because of the $ operator searching through a lot of memo fields records. I say this because most of the FPT files aren't all that big. The biggest one that I actually search frequently is about 22.5MB. I think the performance issues I have are involved with my network and/or the Synology NAS configuration with regard to my network setup. It's odd because sometimes the performance is a lot better than other times, I don't know why. It's something I haven't spent much time and energy trying to figure out... yet.

For this particular "problem" as explained above, I figure to add a field to the table which is an alternate of the one the table is ordered on when printing. In 98% of the records (i.e. all but maybe a dozen or less) that field will be empty. The reports I print are printed from Foxfire! and in there I can design (or redesign) the alphabetical report to give me a join on the original report and one that just includes the records in the new field that aren't empty, i.e. less than a dozen records. That was my original idea. It's not standard database practice, but this is a special case and I think a guerrilla database approach is in order.

 

[Olaf Doschke]

+20MB are easily cached in todays system, but that'll just happen in every second query.

On the other side, I can't follow your idea about how you think to extend your table and/or report. And I think dan also still can't follow your description.

Bye, Olaf.

 

[dmusicant]

Isn't it simple? A join of the records from the original report and the records from that report that have a secondary artist, which is stored in a secondary artist field (which is usually empty). I scoured the table and there are ~9 records that have a secondary artist that I feel I'd like to have represented on a second line in the report.

 

[dmusicant]

I believe this brings up my desired result set where srightalt field (newly added) contains the alternate artist name, and is only not empty when there is an alternate artist name that I want displayed in the report:


SELECT ALL ;
MUSIC.POSITION,;
MUSIC.SONYRIGHT,;
MUSIC.PIONEER,;
music.genre,;
MUSIC.TRACKS,;
MUSIC.MAKENEW,;
MUSIC.ACTUALNUM,;
MUSIC.PIONEER3;
FROM "MUSIC" MUSIC;
WHERE (MUSIC.ARTIST<>"---";
AND MUSIC.PIONEER BETWEEN 1 AND 600);
UNION ;
SELECT ALL ;
MUSIC.POSITION,;
MUSIC.SRIGHTALT AS SONYRIGHT,;
MUSIC.PIONEER,;
music.genre,;
MUSIC.TRACKS,;
MUSIC.MAKENEW,;
MUSIC.ACTUALNUM,;
MUSIC.PIONEER3;
FROM "MUSIC" MUSIC;
WHERE (MUSIC.ARTIST<>"---";
AND MUSIC.PIONEER BETWEEN 1 AND 600);
AND !EMPTY(SRIGHTALT);
ORDER BY 2

** (ORDER BY with field names)...
** ORDER BY SONYRIGHT

 

[Olaf Doschke]

What you have done is a kludge, the only advantage I see is, any already existing queries and forms don't have to change to cope with the new field and you put the two artists into two rows by selecting all old fields and then union that data with the new SRIGHTALT as SONYRIGHT where that isn't empty.

So you want the same record twice just with an alternative SONYRIGHT value? If this has so few occurrances you better really copy the records once with one artist, once with the coartist and cope with the data redundancy than introducing such a union query.

I thought you wanted the artists side by side in the same row, only for those rows/songs having a coartist.

What you can do easier here is really separating the artist (SONYRIGHT) from the main table into a secondary 1:n related table, even though most of the time n=1 only.
A left join of 1:n related artist names does repeat the main record where there are two artists related to the same song. So just the normalisation of data does this for you in a simple way without this hocus pocus and with the ability to even add a third, fourth and so on co artist, too.

Local lcDBC 
lcDBC = Sys(2015)

Cd GetEnv("TEMP")
Create Database (lcDBC)
Create Table tSongs (iID I autoinc, cTitle C(100), Primary Key iID Tag pID)
Create Table tSongArtists (iID I autoinc, iSongID I, cArtist C(100), Primary Key iID Tag pID, Foreign Key iSongID Tag fSongID REFERENCES tSongs TAG pID)
Insert Into tSongs (cTitle) Values ("Valerie")
Insert Into tSongArtists (iSongID, cArtist) Values (tSongs.iID,"Amy Winehouse")
Insert Into tSongs (cTitle) Values ("Body and Soul")
Insert Into tSongArtists (iSongID, cArtist) Values (tSongs.iID,"Tony Bennett")
Insert Into tSongArtists (iSongID, cArtist) Values (tSongs.iID,"Amy Winehouse")


Select cTitle, cArtist from tSongs Left Join tSongArtists On tSongs.iID = tSongArtists.iSongID

I'm not going further down the drain and explain that tArtists should be a third table and The tSongArtists table really should just join tSongs and tArtists as n:m relation by having iSongID and iArtistID in it, just the reference keys to the songs and artists.

This really all is just about proper data normalisation.

Bye, Olaf.