I'm having trouble with a little piece of business logic and I'm thinking that I might put it on the SQL server, but that doesn't seem right to me.
I'm working with an object that has a property that is calculated using the business logic that I'm worried about. Normally, I would absolutely put the code to calculate the property in the business object, but in this case, I need to be able to pull records from the SQL server that have a specific value in that calculated field.
For example:
I have a song object. The song object has a title (string) and can have any number of artists (as in collaborations and duets). On the SQL side, the songs are kept in one table, the artists in another and there is a third table to relate which artists go with which songs.
The song object has a property that is calculated by concatenating all of the artists names. For example, a song with three artists would have the field calculated as "Artist1, Artist2 & Artist3". It is easy enough to make a property that does the concatenation for me. However, under certain circumstances, I need to check to see if there is a song by a certain combination of artists and I'm provided only a single string (I can't change this). For example, I need to decide if there are any songs by "Artist1 & Artist2".
The straight-forward way to find this song would be to load the entire universe of songs and check the calculated property of each one for a match. This would work except that there are hundreds of thousands of songs to go through.
Another idea I had involved parsing the input string to find the component artists and formulating a query based on that, but that didn't feel right either.
Another idea I has was to store the result of the concatenation (as calculated by the business object) in the SQL table as a sort of "key" that I could later use for look up, but that seemed to break normalization.
My last idea was to move the logic that calculates the concatenation to the SQL server where I can make a view that has a field that is the concatenated value. Then I could easily formulate a query that would only grab the records that have the specific concatenation that I'm looking for. This seems easy enough, but I'm reluctant to move the business logic to the SQL server.
Are any of these ideas acceptable? Is there another way?
Right now, I'm leaning toward storing the result of the business calculation (concatenation) in a field on the SQL server and using that as a lookup key. The biggest problem I see with that is that when an artist is changed I have to update the lookup key for all of the songs associated with that artist.
Does anybody have any thoughts or suggestions?
I am no SQL server guru, but it appears to me that once a song has been recorded, there is a zero chance of any change in the names of artists or in their order. In other words, Artist1&Artist2&Artist3 As VarChar() can be looked upon as merely an attribute of the song just like the title.
You have the artists and songs in separate tables along with the third joining table, so you will not be using the Artist1&Artist2&Artist3 column to get songs of a single artist, but only the songs of the exact duo or trio in that specific order. For all other queries you should be using the standard Joins to get at the proper song(s)
Jav
I'm a bit curious about your requirment, as you are refferring to looking up stuff on a string like "Artist1 & Artist2".
If you do this, it would actually mean that "Artist1 & Artist2" is considered completely different from "Artist2 & Artist1".
If the point of the requrment is to actually find songs where both and only Artist1 and Artist2 I would not have gone for the string compare solution.
This is because:
1) As you say, the artists may change name
2) The formatting of a list of artists seems like a requirment that is likely to change (we want "Artist1, Artist2" instead of "Artist1 & Artist2"
3) The solution does not represent the task the user actually wants to peform (wich is to search for songs with Artist1 and Artist2 and no other artists.
So, based on the assumptation (and this is where I might be wrong) I would suggest to parse the input string and transform it into a request for songs where only artists with the given name is contributing.
If the order is important, I would add that as a criteria as well (for example if the artist that is contributing the most is listed first or some other special issue)
As I recall that was also one of your suggested solutions.
I think that my second choise would be to add the calculated value to a separate field in the database but keep all logic in the business layer. Changing a artists name would then, as you mentioned, have to cause all songs where that artist is using to be updated.
Best regards, TEK
Copyright (c) Marimer LLC