Relationship Question - Sample File Appreciated

[JB2]

Hello,

I'm new to using FM Pro 8.5, and have been working to try to grasp how to create a solution I need. I want to make sure I create this correctly so I don't start out on the wrong foot. I just can't seem to get my head around the relationship & table structure I should use.

Here's what I'm looking to do:

Analyze phone usage for individual users on a monthly basis. There would be multiple users with up to 2 phone numbers per person. The usage information will have a lot of different fields (I'll be importing this data from excel). I'd also like to identify the type of device they have. I've downloaded some samples on relationships, and I still cant seem to understand the best way to set this up.

Any suggestions/help is much appreciated - It anyone can develop a sample it would be even more appreciated.

Thanks!

[mr_vodka]

Something like this could get you started.

Users Table

pkUserID (auto-serial)

UserName

etc

UserNumbers Table

pkUserNumbersID (auto-serial)

fkUserID

Phonenum

DeviceType

etc...

UserLog Table

Date

fkUserID

fkUserNumbersID

StartTime

Endtime

etc...

[JB2]

Mr_Vodka - Thanks for the info. I've attached the file created from your suggestion.

On the UserNumber table how is the fkUserID created/brought to this record? Meaning; I entered "John Doe" in the User_Table and he's pkUserNumberID 1, how will the fkUserId field pull this information into the UserNumber table?

Hopefully this makes sense? Again thanks for the help.

user_table.fp7.zip

[Fenton]

Rather than using a special userNumberID, couldn't you just use the phone number itself, as the tie between the Log and the UserNumbers join table. That is simpler, since the phone number is all you're going to get from the Excel file. In this case using an auto-enter ID is just not practical; hopefully the numbers are unique (otherwise how could we call each other?)

[mr_vodka]

You do have a point Fenton, but what happens if one of the phone numbers is a cellphone? One user disconnects their cellphone because they change providers. Then a few months later someone else who gets a new cellphone gets the old number of person 1. I mean the chances are minimal, but it could happen. Thoughts?

[Fenton]

Then you would remove the number, as soon as the first user stopped using, and add it to the other user, when they started using it. From what I've seen of phone logs, all they have is the number. So that is what has to be used. It would be good to test for non-unique entries in the join table, so you would be alerted that something like this has happened. You'd notice when the 2nd user added that number.

[JB2]

I think the assigned number would be the best way to identify the user as well, as long as one user could have multiple numbers...I'm just not sure at all how I sould go about setting this up?

[Fenton]

I spoke a little too soon, or too shortly. I think the link between the join table and the calls data (imported from Excel) should be on the phone number, as that may be all you get back. If you also get the User in the calls data, then that's useful. But if you do not, or if the name is questionable, then the UserID can be looked up into the calls data, via the phone number.

That gives you a permanent link to the person for the call. Then it will not matter if a join table record is deleted, when a number is no longer in service.* And a new join table record with that same phone number, but for another person, will not cause a problem. Because the earlier lookup into Calls has already happened and will not retrigger.

You would also want a relationship directly from Person to Calls, based on the UserID.

*Alternatively you could leave the record, but have a field to deactivate it, also a date. That way you'd have a history of numbers a person had. But this info is also available from the Calls themselves, if you lookup the UserID.