1 of 1 people found this helpful
Your relationship would link one record in T35b to possibly many records in T35.
Thus, placing Date1 from T35b on a layout based on T35 will show either data from one record or no records depending on the values in your two match fields.
So your relationship may or may not be correct as I don't know if you want a 1 to many relationship from T35b to T35.
Date1 could be empty or the value in T35b::id_school_dates might not match to any record in T35. So your relationship could be correct but the field Date1 could be empty or the data needed to match the records might not be there.
Okay, that's helping me better understand how it works.
How the fields relate is the part I get confused about, because logically, how would the record pulling data know what record to pull it from?
I just read about the different types of relationships, and it kind of makes sense, but I need to see an example to fully have it.
This might help sort out what kind it is:
Table: School Dates
There are 61 records in School Dates. Each record has Date1. So that would be one field with multiple rows in the table.
Table: School Days
Field Date1 (from School Dates)
I'm looking to create 61 records in School Days with several related fields, that match each of the records from School Dates.
I hope this helps. Is this a many to many? One to many?
Wait...is this a joined table?
Your choice of terminology is EXTREMELY confusing.
Something is one to many, or a join table, or something, but even though this is YOUR data and your concept; you don't know?
What does all this represent?
Also, FWIW, for foreign keys, it might be a tiny bit clearer to include the "f"
FK: IDf_school_dates - text, Indexed (all)
I completely understand your consideration. Not being familiar with our company and the terminology, it would be confusing to an outsider.
"School Dates" really means "Classes," or "schedule."
"School Days" represents the actual day at the track and the details of that day. For example, what students are there, their level, if the turned in paper work. This table also includes lots of record keeping for what occurred on that day. Did anyone crash? What was the weather like, did everyone complete, any future service notes, etc.
These are the terms they use, and have been for the last 38 years.
I thought my descriptions in previous posts would make it clear what I was trying to achieve. Reading my last post, I spoke using current pop-culture. I can see how it would be mis-interpreted and made confusing. I tried to edit it, but couldn't find the option and frankly, I'd rather spend time in FM.
I wasn't asking the community to tell me what my data is. I was asking if what I was trying to do, fits the description of a joined table...then I'd go down that route. I'm learning all this as I go.
Got it on the "F" and "P." I'm using FM Starting Point by RC Consulting and that's what they do. I agree, it would be easier, but I want to keep everything the same.
Bruce makes a good point. It's just good documentation, and a standard to indicate in the "related" table the table the key came from -- as part of the field name. So, if you have an ORDERS table you would include the CUSTOMER PK in the ORDERS table so you would know which customer was for which order. So using a field like ORD_CUSFK, or ORD_CUSID, would make that field's meaning clear (as is the standard) to any DB person. Notice that in the CUSTOMER table, each field starts with "CUS" so the foreign key portion for the CUSTOMER PK in the ORDERS table "ORD_CUSFK" clearly documents that.
Not giving you a hard time, but I also agree with BruceRobertson's other point: Having clear table names that are "self-documenting" (as much as possible) will help immensely with maintenance and having other developers not necessarily need to know all the "inside baseball" just to understand the data model.
I think you know this already, but deciding the cardinality of a relationship between two tables is really only asking yourself if "one of this" (in one table) can have "many" in the child table. Of course, it's a bit more involved since you could have zero, 1, or many, too, in the child table, but it's more or less the same thing -- depending on business rules where you could have a CUSTOMER, say, but no ORDERS yet. The cardinality is still 1:M, but in an ERD, you would model that as 1 to "zero, one, or many".
HOPE THIS HELPS.
Point taken. In future projects, I'll change the anchor-buoy naming convention. It's not worth changing the whole project at this point. Most likely the future developer will be me, or the company who created the base system.
I guess I could see that point too. Externally they can call it whatever they want...even on the layouts. But for development sake, the table names should be "self-documenting."
I kind of knew that...still learning. So when other describe it in a different way, it helps cement the idea.
I think my original question really never got answered. Some of the replies got me thinking about how to structure the tables and relationships. I'm also going to get some professional coaching, so I'm sure I'll figure it out.
Will post on here what the solution was...to help others.
Wow, the license plates in CA were a LOT nicer at that time!
Wow! That is classic shot.
That track is in my top 3, most dangerous tracks to ride.
Crashed in turn 7, broke only my left little finger. Figured that was enough, just rode Trials after that.
1 of 1 people found this helpful
I'm sure you probably know what I'm posting below, so please excuse the simplistic example.
When I have problems like the ones you're having, I always go and create a simple working example -- the simplest thing I can create to make sure I have the basic idea working correctly. If that works, I add on until I find out where I went wrong.
So, with that caveat....
If you create a table like this:
and an orders table like this:
ORD_NAME (Normally, we would have an ORDER_ITEMS table here, but for this example, omitted it)
With data like this:
1 1 1 "ORDER 1 CUSTOMER 1"
2 2 1 "ORDER 2 CUSTOMER 1"
3 2 1 "ORDER 1 CUSTOMER 2"
Then, in the Relationship graph, link the CUS_PK to the ORD_CUSFK, like this:
Create join settings like this:
Now, create a Portal on the main layout and add fields from the related table (ORDERS), like this:
After adding a few records, I see this:
Scrolling back to the first record, you now only see those related records from ORDERS.
Of course, in a real application, you probably would never display the CUS_PK in the related table (ORD_CUSFK as the portal field), but I left it here for an example.
Hope this additional example is helpful in some way.
Wow, take me back to my own road racing days...