Datamodelling beyond 1:N ...

[FFF]

Not sure, if the following is approriate here, but i tend to think clearer, when i have to write down a problem, so bear with me
I'm building an app to manage musical events, strictly inhouse use and for our times, "miniature" table sizes and almost no need for super performance . The idea is primarily to hold almost any information in the db, later on to generate all sorts of files which are needed...

A certain part is clear enough, you have seasons, concerts, locations, ensembles, artists, instruments, composers, opera...
The relation between concert, ensembles and artists is by nature N:M, so i'll need intermediate tables.
Now, there are e.g. concerts with
* one artist,
* with N artists, which form an ensemble,
* with N artists which don't do this,
* with N ensembles and
* with N ensembles plus M artists, which don't belong to any of them.

Somehow i'm stuck how to model this, and especially, how to build a gui for maintainance.

Helpful thoughts anyone?

[ArneOrtlinghaus]

May be you can put together Artists and ensembles in one table: Performers: There you can add information, if this is a single person or an ensemble or a juristic institution like an event organizer who has to furnish some artists. Or may be people like mixers or light technicians. This table needs a sub table Ensemble performers that links to the same table so that you can create ensembles.
You should rename concerts to "events"

You can add a table "Event performers" that has a foreign key to the event and serves as a sub table for the events. This is the N:M-intermediate table. Then you will have a browser in your GUI within your concerts with these performers where you can add some information like "Wants to participate" or "Cannot", Contract made, paid, ... And of course also the current role for the concert. May be a musician makes the mixing for a current concert.

When you add an ensemble as performer, you can add a button "Resolve ensemble as single artists", so that the ensemble can be substituted by its current artists.

Arne

[ic2]

Hello Karl,

In addition to Arno's reply: if you create a Performers table you can make one of the following choices:

1 The performer is the entity that gives a concert and gets a contract. It doesn't really matter which combination of artists/staff performs. The same combination may be re-used for multiple concerts but if there's a significant change you can just add a new performer even if that performer consists of most of the members of an earlier performer. A good sample would be Crosby, Stills, Nash & Young who have performed in almost every combination, from solo via duo to all 4 of them. So your performers database can contain "Crosby, Stills, Nash & Young", "Neil Young", "The Stills-Young band" etc.

2 (Only) if individual details are important you can create either a subtable where Crosby, Stills, Nash & Young have one record each or in the performers table itself. Then you can create a 'link' table where you link 1 performer for a certain concert "Crosby, Stills, Nash & Young" to the records for the individual artists. The latter is especially important if you need to store details for each member (like what kind of drinks and food should be present if we keep the sample a bit in the 'superstars' comparison). Then any change in these details need to be done only in 1 records.

In our software, we have a contact person database but gradually we got cases where the same contact persons was working fore a new company. I never bothered to redesign the Customer/supplier - contact relation properly and just created the same contact person once again for the other company. In some cases this had advantages (e.g. the contact database fields email, phone and more were often different for that same contact the other company). Drawback is that if some general info changes (like a home address, for the few contacts where I know that info), it has to be changed in more than one record.

Finally, I created a table called links. This simply contains 2 keys fields which I can fill with anything with a 1 to N or N to 1 relation. I could have solved the above contact problem by adding 2 relations (e.g. 100 and 200) and 1 contact (say 150) as follows: links tables contains 100-150 and 200-150 so the correct (same) contact 150 person for the 2 relations 100 and 200 could be retrieved via the links table. I could even have chosen to store and retrieve variable info (like that mail or phone fields) in a separate general table or even in the links table with some design changes.

As said, I didn't. But I hope this gives you some ideas.

Dick

[lumberjack]

Hi Karl,

Hmm not sure if this is a direction you would want to persue, but have you looked at how I implemented my Datadriven model? It sure would be able to cater for than. A four table solution, but you will have to do a little bit of generic coding for it to work. Doable and once you have all in place I have been able to use it almost in any scenario. Send me a private mail if you want to discuss it.

HTH,