Jump to content

I messed up my relationships


Chris Echols
 Share

This topic is 5705 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Being a newbie, I always start databases with templates and work my way up. I started using a contact database for something I'm doing on the side, but then figured out that I needed to use the P.O. template to make a quick invoice for services rendered. Then of course I thought about all of the other things I needed to track. (I didn't plan it out.)

Luckily, I don't have a lot of records in this database, so I can afford to start over with a plan, but I'm stuck on step one - the ERD.

I have contacts and there are many contacts at different churches or venues. I perform observations at the different venues, but then I ran into one church that has 2 venues. If I have to do an invoice or P.O. for the church I'll need to keep track of services rendered and equipment suggested. Now I want an equipment table to be related to both the invoices or course, but also to the observations (where I make a list of all the equipment that I observed and/or tested).

I know I'm probably wanting to do a whole lot with this database, but that's why I have filemaker, so that I can... See the attached pic and let me know where I'm messing up.

Picture_1.jpg

Link to comment
Share on other sites

You haven't really said what's messed up. If you want to have multiple relationships to various tables, use multiple TOs (table occurrences).

I'd suggest coloring the TOs so that all TOs of the same table are colored the same. This makes it easier to see what the underlying tables are.

Also, turn on the auto-enter serial numbers for your tables' primary keys. This will turn your relationship lines into one-to-manys, making it easy to see what the parent-child relationships are.

Link to comment
Share on other sites

Same thing happened to me last year... The worse things was that i kept calling like every hour and they kept telling me that it would be there first by 12, then by 3 then by 5... After I fianlyl called them at 5:15 and said I couldnt stall her any longer, they were like, I dont thin kwe will get to it today but we could deliver it tommorow. I was so furious with them.

I feel your pain Stuart. :frown:

Link to comment
Share on other sites

You haven't really said what's messed up. If you want to have multiple relationships to various tables, use multiple TOs (table occurrences).

I'd suggest coloring the TOs so that all TOs of the same table are colored the same. This makes it easier to see what the underlying tables are.

Also, turn on the auto-enter serial numbers for your tables' primary keys. This will turn your relationship lines into one-to-manys, making it easy to see what the parent-child relationships are.

OK... that helped me figured out the first thing I was doing wrong (I'm new to relationships). I saw a tutorial that told me to try to have only one to many or many to one relationships. So now I've figured out that the company/venue table needs to be a parent of Observations and Contacts, while the Purchase Orders table is a parent to Vendors and equipment. In my first post I was torn between whether to have a venue table and a separate company table, or whether to somehow just make myself a company/venue table so that the few companies that have different venues would just have more than one record. The concern being, that just because it was a different venue, doesn't mean that there would be different contacts. I'm still trying to wrap my head around the "similars" keys used in the demo contact table.

After fixing my "primary" keys, I realized that I needed an invoice table to be a parent to Purchase orders since I would have a client (Venue) that would need to make purchase orders from various vendors to complete one project that I would send them one invoice for.

Now I just need to redesign the layouts to take advantage of these relationships. I think, which will surely bring me back here. Last time I did a portal, I couldn't get it to do what I wanted to do, but I can address that later.

One table I'm not clear about is the VendorEquipment "join" table. I know that vendors can have lots of equipment and I know that equipment can be carried by lots of vendors. But which table determines (or should determine) the device ID, the Observation table, or the Purchase Order Table? Is it possible to have two parents?

In the observation table, it really is a list of equipment present at the venue.

So now my final questions are:

How do I deal with the relationships between services and PO's? Is it ok to have the many to many relationship there?

Picture_1-6.jpg

Link to comment
Share on other sites

Well, I thought I could use my Venue Form as the main form and just add new contacts through the portal, but I'm finding that it is not a straightforward as I thought.

How can I ensure that new contacts I want to make are correctly associated with the venue I'm working on? I'm playing around with the scriptmaker to make this work and trial and error is killing me right now.

Anyway, thanks for your help so far...

Link to comment
Share on other sites

So, after RTFM, I'm well on my way to making this thing do what I want it to do, but as with anything, I keep wanting it to just one more thing. So disregard the above post where I could get the portal thing working. Now I need to know how you guys handle fields in those related databases that are the same. If I got the address in one table, do I really need it in another table? I think I do for various reports and what not, but maybe that's just me being anal.

Since I can get at any related field from any layout, it seems that I can do without the redundant address fields...

What is a good rule of thumb?

Link to comment
Share on other sites

This topic is 5705 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.