Design Dilema

[amigos]

I want to manage my DVD collection (documentries), and my dilema is how to go about it.My DVDs would be purchased or recorded. The recorded DVDs could contain many movies, some fields in purchased would not apply to recorded ones. I attache a sketch of my 2 tables which I am after. Any hints in fundamentals how I can achive it, please!

[Vaughan]

You've probably got to separate information about the movies themselves (Titles) from the media they are stored on (Disks). Just about all the information you list above is either Title or Disk related.

Since a Title can be on more than one Disk, and a Disk can have more then one Title on it you're going to need "join" file. In Library cataloguing circles it's usually referred to as a "Segments" file. Titles can be split into multiple segments, and a disk can contain multiple segments.

So you need three tables, Titles, Segments and Disks.

A large part of the design of your database will be determining your own "business rules" that is, what you deceide you want to call things and how you want to categorise them.

[amigos]

Thanks a lot Vaughan !

I'm gonna do some work according to your tip.

If you curious what I did before I posted a topic, take a look at attachment.

I was not happy with design, however this is the closest picture how I want the things work.

Movies.fp7.zip

[Fenton]

You need the join table. The mulitple DVDS per Movie, or Movies per DVD appear in a portal, with "Allow creation of related records" and "Delete related records" to the join table (not to the main tables!).

The join table, at its most basic, contains only 2 ID fields, linked from each of the main tables.

In the portal the names appear by reaching thru the join table to the other main table. The field is usually not enterable (as you'd be changing the name in the main table).

To "unlink" a join, you just delete the join table record. No other data is deleted. That join would then disappear from the portal in either main table.

Navigation from inside the portal is basically Go To Related Record [ through the join to the other main table ]. Whether you "Show only related records" or not is really a business decision. Whenever you go to only related records from inside a portal, it goes to all records matching the relationship, but ends up on the record of the particular row you click in.

I added a "film" button. Check the buttons.

(P.S. This is all assuming that, as Vaughn said, you also sometimes have movies that span multiple disks, AND each of those disks would have its own DVD entry. Otherwise you only have a one-to-many from DVDS and do not need the join table.)

Movies.fp7.zip

[amigos]

Thank you very much Fenton !

You are great man and your work is very helpful. I am getting a step closer.

I study it, and now I know the one-to-many is only needed, because I do not buy the same DVD, or do not record twice the same movie, so a movie can appear only once, but recorded media can have many movies of different title.

I like the DVD detail layer you did it. I think this layer would work nicely for me as a fronted, but if I remove a joining table, the portal will be gone.

I am gonna sit down tonight and play around hard.

PS. Thanks for a link to FileMaker 101 Tutorial Series

Edited June 8, 2007 by Guest

[Fenton]

Without the join table, you would only have a portal on the DVD layout. On the Movie layout you'd just have a DVD field(s), as there's only 1 parent DVD.

You can see multiple Movies in the portal on the DVD layout. But it becomes more awkward to "choose" a movie. You could "create" a new movie and add it to the DVD easy enough, by turning on "Allow creation of related records". But you would need another mechanism to "add" an existing movie; something like a "dwindling" filtered portal, of movies not added to any DVDs yet.

So, since I don't have time right now to add that, I left "allow creation" off. You will only be able to add a movie by going to the movie and choosing the DVD.

Also, major difference from the join table method. When you "unlink" using a join table, you can just delete the join record. When you're going directly to the child data table instead, you certainly do not want to delete the child. What you're doing when you "unlink" in that case is only clearing the parent ID in the child foreign key field (DVD in this case).

Re: FileMaker 101. That was a pretty good series, for its time. But it was written long ago. Most of the info is still valid, but some may not be, and there would sometimes be more modern ways to do some of it. I'm really a slouch when it comes to updating my web pages :-|

Movies_fej.fp7.zip

[amigos]

I made some progress: "allow creation" has been added, is a little bit primitive, but works well for me.

I'm going to enter the data into database shortly, but before I do it I would ask you the last 2 questions:

1. Your UNLINK button created in portal (DVD Detail), does not delete the child, only Movies::DVD_ID is cleared.

I am confused a bit because Movies DB does not have a joining table, and you said without it, child is deleted. I don't want a child to be deleted, only unlinked. I think what you did works very well. Can you please verify this, because this is the most important think to settle before I start any data entry.

2. Does primary key must be always a NUMBER type ? What about text (unique), or is it silly approach ?

Thanks for your time, Fenton! and hope I start enjoying soon my first database, and won't record anymore the same documentries twice.

Movies-SemiFinal.fp7.zip

[Fenton]

1. The only link between a movie and a DVD is the DVD_ID in the Movie table, ie., a movie can only be on 1 DVD. So deleting the DVD_ID in Movies (which you can do directly via a button in the portal) unlinks it.

2. Text or number, it doesn't matter a whole lot. Number takes up a little less disk space, but that's not a big deal.

So your DVD_ID is coming from the DVD itself?

[amigos]

Yes. Before it was stored in SN field.I have removed it, and now the SN and DVD_ID serves as a one.

DVD_ID will always be entered manually based on Barcode (if Original), or Calculation (date of burning in case of recordable dvds), that way I will be able to keep it unique.

Why I did so ?

I did NOT like an interaction of "Part of..." field (Movie Detail Layer). Now I can look up DVD by its Serial, and before I had some 1,2,3... I did not know how to fix it, or deal with it, so I did easy way.The recorded DVDs has no title (only movies on it), so it works well in my case.

Is it something really wrong with it ?

PS. Thanks for a reply, Good Day to you!