newbie questions about relationships and parent/child tables

[kilikina]

I am new to File Maker (using version 8) and it's been awhile since I've designed a database, so I'm hoping some of you can help me out. I've only ever used Access and some things are tripping me up. Maybe I've created them incorrectly or not in the smartest way, so help would be greatly appreciated.

I have the following tables: Customers, Members, Plays, Contact, Invoices, and Payments. Customers and Members are the only tables that do not have to relate to one another. Within this organization, Members are playwrights who sell the rights to their plays through us. Customers are individuals or organizations who purchase the rights to a play. Therefore, Plays are really what link the two together.

The relationship between Members and Plays would be one-to-many. One member can have more than one play with us. The same would be for Customers to Plays: one customer may purchase more than one play. Plays do not exist without a Member attached to them, as we do not carry Plays for non-members. Contact, Invoices and Payments should be related to all tables. Members are invoiced for their membership fees and customers are invoiced for royalties.

This is how I have designed each table. Each has its own ID as the primary key. I have the IDs set up as Text, Auto-enter Serial Number, Can't Modify Serial, and Unique Value. I have named all primary keys and foreign keys like this (for example): kp_Contact ID, kf_Member ID, kf_Customer ID, kf_PlayID etc...

My IDs are like this MEM000001, PLAY000001 and so on.

How should I set up foreign keys in a table? Right now I have them as text only. Is this right? And how should I set up the relationships? Should all the primary IDs = all the foreign IDs? Should I allow creation and deletion of records between the tables? I'm having trouble figuring out what exactly are my parent tables. Since both Customers and Members have to have contact information, should I make it the Contact Table?

In the past with Access I created a database with my Customer Table as my parent table, and the Customer ID was used in conjunction with other IDs in other tables as the primary key (I forget the technical term for this - when a primary key is made up of two unique numbers, one of which comes from another table). When the user entered the Customer ID, another ID was automatically assigned and the person's information would appear. I was thinking the same thing should happen with this one. For example, for Plays, that the ID is made up of both the Member ID and the Play ID. But I haven't exactly figured out how to do this and am wondering if it's a smart design in FM or if there is a better way.

Hope this makes sense.

Thanks in advance.

[The Shadow]

Personally, I just use numbers for serial numbers.

You're going to need intermediate tables to hold the many-to-many relationships.

I put together a sample of how I would start constructing the graph, look in define database relationships to see a possible setup. You would hook a normal invoicing system onto that for each member/customer.

Hope this helps.

PlayMaker.fp7.zip

[mr_vodka]

Personally, I just use numbers for serial numbers

Hi Shawn,

I figured I would jump on this thread since you replied to it. First, I use numbers for serials in my solutions as well. However, there has alawys been something that I was curious about. Do you know if there are any speed differences between number key fields as opposed to text? IOW, is there a speed difference when using a serial such as 999 verses Cust999? I figured you would be a good person to ask. :

[Vaughan]

Prinary keys should, ideally, have no inherent meaning. The users should never have to see them either. So Cust999 is ok, but since they are never seen what is the "Cust" for? :

I used to make the serial number fields text. Until one day I sorted a large table by the primary key and exported, only to fint he numbers sorted alphabetically not nummerically (1, 10, 11, 2, 21, 22 etc). I make them all numbers now, and this precludes any alpha prefixes.

[LaRetta]

...but since they are never seen what is the "Cust" for?

There have been times that I've combined unique IDs within the same field (as multiline). It can be a powerful technique (learned from Comment) and prior I've seen it used by CobaltSky (beginning the serial with the ID type, I believe for the same reasons). I don't do it in all cases but, depending upon the complexity of the solution, it can allow more flexibility. I've used 'unique ID combination' fields in my LineItems which worked a treat!

I always have a unique serial (number) but sometimes I create a text concatenation (calculation) from it as well to use in different situations (padding the numbers with leading zeros).

LaRetta :wink2:

[kilikina]

Thanks for the advice. I changed my IDs to serial numbers as suggested and have set up my relationships. The only one I haven't touched is my Invoice Table. I've never had to design an invoicing system. So what would a standard system look like? Do I have to create an Invoice Line Table? One thing with the serial number is that my organization would like for the Invoice number to reflect the year and automatically reset to reflect the change in year. For example, all the invoices for 2007 to be something like INV07-00001, for 2008 INV08-00001 and so on. Is this possible and how would I do that?

[mr_vodka]

I agree to some extent. I have for the most part always used numbers. This is just because my logic tells me that numbers would be faster than text. That is why I posed the question regarding speed. I am wondering if that is a wrong assumption on my part.

Here is an example of why I am even considering changing my keys to text...

Lets say I have a Documents table. This documents table stores the docs of many different modules. So if all primary keys to the various modules were number only, when the foreign key is stored in the documents table there would be conflicts. Now, a concat field could be made but then this brings us back to the orig scenario. There would not really be a different between the two and I dont see the purpose of creating a second field just for that sole purpose. So if I am forced to use a text field to match to the foreign text field, I was curious about the speed differences.

[The Shadow]

Its fine for the Invoice to have a user friendly invoice number like that, but you shouldn't use that as your internal primary key, I would have a hidden serial number id field that was also not shown to the user.

Since both a playwright and customer can have invoices, I think it would make sense for the invoice to connect to the contact table in this case. The normal setup for invoices includes the tables Accounts (maybe Contacts, for you), Parts (Plays in your case), Invoices, and LineItems. I'm sure a quick search on this site would come up with some excellent examples for it.

[The Shadow]

My background would lead me to believe using numbers would in general be faster. The indexing for numbers is simplier than for text - numbers are indexed solely by their numeric value, while text can have several indexes: the minimal setting in storage options will give value only, which is good for joins and valuelists, there is also a seperate word index for searching on individual words, and relaxed word/value indexes for japanese relaxed searches.

Also, if you allowed the user to see your id field and search it, a search for 123 is going to create the word index for that text field if you have auto create indexes set, and will find anything with that prefix (1230, 12379, etc, etc), which is probably not what was desired.

People used to use text fields for keys because they were worried about numeric overflow of their serial numbers, but now that numbers go up to 10^400 or so that excuse is now gone. ^)

[mr_vodka]

This is great stuff and precisely the type of feedback that I had hoped to get from you. FMI should try to steal you back.

Thanks again Shawn.

[Ender]

Hey Shawn,

On a related note: if a multi-key is needed for matching a list of IDs to a RecordID, is there any type mismatch problems when matching the text multi-key with a numeric RecordID? Or should the RecordID be made text in these situations?

[The Shadow]

Hi Ender,

It's been a while, but as far as I recall, each key on the source side (current layout side) is converted to the type (and locale, if languages differ) of the destination field for matching purposes. While this might be slightly slower if the types don't match, I doubt it would be noticable - the join itself is likely to take much longer than this conversion.

You can try this stuff out as an experiment:

This file has joins between number and text keys, and its the destination (portal) side that decides the type for the match.

JoinExperiment.fp7.zip

[The Shadow]

This is great stuff and precisely the type of feedback that I had hoped to get from you. FMI should try to steal you back.

Well some there would like to, or at least I like to think that.

But now I work on Aperture's database instead - its good to have some change to stir up the pot now and then.

[comment]

Thanks, Shawn, this is very interesting. Following this logic, it's possible to set up a curious contradiction:

ParentValue (Text) = "A1"

ChildValue (Number) = 1

The result is that the Parent has a Child - but the Child does NOT have a Parent.

[Vaughan]

Have you tested this or is it a thought experiment?

[Stuart Taylor]

That is really interesting. Kind of an "orphan" relationship.

http://en.wikipedia.org/wiki/Orphan

[comment]

Given that the parent is alive and well, and knows of the child, but the child is unaware of the parent's existence, I would think of another term as more appropriate.

[Stuart Taylor]

LOL, thats probably what i would call it if i couldn't work out why my relationship wouldn't work. :)

[kilikina]

Its fine for the Invoice to have a user friendly invoice number like that, but you shouldn't use that as your internal primary key, I would have a hidden serial number id field that was also not shown to the user.

Since both a playwright and customer can have invoices, I think it would make sense for the invoice to connect to the contact table in this case. The normal setup for invoices includes the tables Accounts (maybe Contacts, for you), Parts (Plays in your case), Invoices, and LineItems. I'm sure a quick search on this site would come up with some excellent examples for it.

That's how I have it set up - Invoices connected to Contact table.

Items on the invoice can be Member Fees, Royalties, Books, and Subscription Fees to our magazine. If it is a Book or Royalties, then I need that item to be associated with the Play Title and ISBN #. Now I already have the Play Title, ISBN #, Royalty Rates and Book Rates stored in the Play Table. Can I make it so that they are taken from the Play Table? Rates are not standard and vary between each playwright and each play.

What I would like is the user to be able to pick from a drop down menu the item (member fee, royalties, books, or subscription fees), and then based on their choice have fields give them other options to choose from. For example, if they choose Membership Fees, then I'd like the next field to produce a list to choose from that offers the different types of memberships, and once they've chosen that, I'd like the price to automatically fill in. The same goes with Royalties...if they choose this, I'd like there to be a field where the user can type in the title of the play and have the ISBN # and rates automatially fill in.

How exactly would I design this?

thanks,

k.

[Stuart Taylor]

Hello,

Here is a post by John Mark that has a good example of conditional value lists

Link

[The Shadow]

I think I have an example something like what you want on my website:

http://www.spf-15.com/fmExamples/

Download the "simple invoicing" file to see an example of an invoicing system setup where you choose a book by title and the cost and bookId are filled in by lookups.

Hope this helps.

Edited May 22, 2007 by Guest
Fixed URL

[Stuart Taylor]

Hello,

One 2 many t's in the URL (htttp)

http://www.spf-15.com/fmExamples/