So I just converted the main database, and will put the other related files as
other tables in the same big file. It does seem to save space, but that doesn't
really matter to me about that.

My question is about field defintions. I have, fer example, [Email] as a field.
There was an email field in the 4 separate ver6 files of Students, Professors,
Community, and Program_Assistants.

Now after combining all 4 files into 1 file with 4 tables, I will have 4
instances of the [Email] field, which I can see in the "Database Design" page.

Is that the right way to do it? Or is it efficient database design to have one
field [Email], and 4 different instances of data, depending on the Table.

I am a little confused because each table can have its own fields and records,
so that makes me think that I have to have separate [Email] fields, as if the
Table were separate files. Is that how I should think of the Tables when doing
relationships and design?
--
"... respect, all good works are not done by only good folk. For here, at the
end of all things, we shall do what needs to be done."
--till next time, Jameson Stalanthas Yu -x- <<poetry.dolphins-cove.com>>

In article <f9i685$bjc$1 [at] gist.usc.edu>
~consul<consul [at] INVALIDdolphins-cove.com> wrote:
> So I just converted the main database, and will put the other
> related files as other tables in the same big file. It does seem to
> save space, but that doesn't really matter to me about that.

> My question is about field defintions. I have, fer example, [Email]
> as a field. There was an email field in the 4 separate ver6 files of
> Students, Professors, Community, and Program_Assistants.

> Now after combining all 4 files into 1 file with 4 tables, I will
> have 4 instances of the [Email] field, which I can see in the
> "Database Design" page.

> Is that the right way to do it? Or is it efficient database design
> to have one field [Email], and 4 different instances of data,
> depending on the Table.

> I am a little confused because each table can have its own fields
> and records, so that makes me think that I have to have separate
> [Email] fields, as if the Table were separate files. Is that how I
> should think of the Tables when doing relationships and design?

I'm not sure I follow the problem. I know I don't get what you're
describing in the next-to-last paragraph.

If space is not a concern, having an email field in each of four
tables is certainly not something to worry about. You're doing that
for other similar data (e.g., name and phone number, etc.), right?

In terms of efficiency, it makes more sense than yet another table and
setting to set up a relationship to keep track of which address is
whose.

For that matter, you could get by with a single table to contain all
four groups, with a field to identify which group the person is in,
and use separate Table Occurrences to separate them based on
relationship criteria. If you have people who belong to more than one
group, that would be a savings, as the group identifier field could
hold as many values as you have groups. You would need only one record
for each person instead of one for each group he belongs to.

Having the TableName::FieldName construct does help in knowing what's
what, though, so I think I would probably stick with that.

Matt

--
Free FileMaker Technique Demos: http://www.VirtualVermont.com/FMP

My Custom Functions: http://www.briandunning.com/filemaker-custom-functions/resul ts.php?keyword=wills

and thus Matt WIlls inscribed ...
> I'm not sure I follow the problem. I know I don't get what you're
> describing in the next-to-last paragraph.

Yeah, I didn't word that right. Letmetryagain.

> If space is not a concern, having an email field in each of four
> tables is certainly not something to worry about. You're doing that
> for other similar data (e.g., name and phone number, etc.), right?
> In terms of efficiency, it makes more sense than yet another table and
> setting to set up a relationship to keep track of which address is
> whose.

There is one Database, with 4 Tables. Each Table has the [Email] field. Each
table has it's own set of other fields and record counts in them. I was thinking
like it was for Repeating Fields, where one could have 3 different values in a
Repeating Field for say phone numbers, instead of having 3 separate phone fields.

In fmp7, I can choose what tables and fields to choose my fields from. I
mistakenly thought that I could have it: with one [Email] field, sharing it
across 4 Tables.

> For that matter, you could get by with a single table to contain all
> four groups, with a field to identify which group the person is in,
> and use separate Table Occurrences to separate them based on
> relationship criteria. If you have people who belong to more than one
> group, that would be a savings, as the group identifier field could
> hold as many values as you have groups. You would need only one record
> for each person instead of one for each group he belongs to.

Each table would no identical data in them. I had my question because while
there is no data overlap, there is field name overlap. W/ no data overlap,
multiple tables seemed the way to go. I know I could have kept it in multiple
files, like I had in fmp6, but I was giving Tables a try. :) I would prefer
separate tables/files so that I can update just one group at a time, if necessary.

Oh, the only identical data would be matches or calc fields like
"Semester_Course_Active" which would be how some of the relationships would be
created.

> Having the TableName::FieldName construct does help in knowing what's
> what, though, so I think I would probably stick with that.

I've got time to play around with this conversion. Thanks for some ideas.

At some other far point in my fmp 7-9 learning curve, I'd like to try out the
separate data/layout files that I heard some folks muse about a couple of years
ago when 7 came out.
--
"... respect, all good works are not done by only good folk. For here, at the
end of all things, we shall do what needs to be done."
--till next time, Jameson Stalanthas Yu -x- <<poetry.dolphins-cove.com>>

In article <f9i685$bjc$1 [at] gist.usc.edu>,
~consul <consul [at] INVALIDdolphins-cove.com> wrote:

> So I just converted the main database, and will put the other related files
> as
> other tables in the same big file. It does seem to save space, but that
> doesn't
> really matter to me about that.
>
> My question is about field defintions. I have, fer example, [Email] as a
> field.
> There was an email field in the 4 separate ver6 files of Students,
> Professors,
> Community, and Program_Assistants.
>
> Now after combining all 4 files into 1 file with 4 tables, I will have 4
> instances of the [Email] field, which I can see in the "Database Design"
> page.
>
> Is that the right way to do it? Or is it efficient database design to have
> one
> field [Email], and 4 different instances of data, depending on the Table.
>
> I am a little confused because each table can have its own fields and
> records,
> so that makes me think that I have to have separate [Email] fields, as if the
> Table were separate files. Is that how I should think of the Tables when
> doing
> relationships and design?

I suspect, from what you are saying here, that you have not
conceptualized your tables the best way. If so, what I am saying would
have been true in Filemaker 6 as much as it is true in 7/8/9.

Of course you can do it however you want, but the standard approach
would say you should only have one table (or file in FMP6) for all of
the data that you have mentioned.

It sounds like students, professors, Program Assitants and Community
Members are all PEOPLE.

I think you were using your separate files (in 6) and now your separate
tables within one file (in 7) to handle the fact that these are
different CATEGORIES of people and you wanted to be able to work with
them separately. And perhaps there are certain fields that only are
relevant to one group or another.

That's not really what you are supposed to use separate tables for.
You are supposed to use separate tables for entities that are entirely
different on a logical level, like PEOPLE and CLASSES. Then you can
have relationships between the tables, so you know which people are
associated with which classes.

I believe that you should just have all of your data in one table called
people, and then you can have a field that designates what kind of
person is represented in each record. There may be some fields that
are only relevant to certain kinds of people. If so, you can leave them
empty when appropriate.

What I am describing is the "correct" approach to database design. If
what you are doing is VERY simple than it may be more convenient for you
to keep it the way you've been doing it. But the question that you
asked, about wanting to "share" the email field among the different
tables points to the fact that you should really just have one table.

Greg

and thus Greg Dember inscribed ...
> ~consul <consul [at] INVALIDdolphins-cove.com> wrote:
> I suspect, from what you are saying here, that you have not
> conceptualized your tables the best way. If so, what I am saying would
> have been true in Filemaker 6 as much as it is true in 7/8/9.
> Of course you can do it however you want, but the standard approach
> would say you should only have one table (or file in FMP6) for all of
> the data that you have mentioned.
> It sounds like students, professors, Program Assitants and Community
> Members are all PEOPLE.

No, I guess the titles were misleading. Students are my students, about 1200 who
are in my program. Professors really means the Course info, of which there may
be about 80 profs working in 300 courses. Community are locations, and Program
Assistants, are supervisors.

Students was the main file (aka originally called 'Macrofile') and they would
sign up for various Courses_Professors, be assigned to volunteer in various
Communities, and be overseen by ProgramAssistants. The Student file then has a
bunch of relationships b/w the files.

I hope I was clearer this time. I think I was just being unclear as to what new
thing I was trying to convey, when in reality, it was the same concepts as before.

> I believe that you should just have all of your data in one table called
> people, and then you can have a field that designates what kind of
> person is represented in each record. There may be some fields that
> are only relevant to certain kinds of people. If so, you can leave them
> empty when appropriate.
> What I am describing is the "correct" approach to database design. If
> what you are doing is VERY simple than it may be more convenient for you
> to keep it the way you've been doing it. But the question that you
> asked, about wanting to "share" the email field among the different
> tables points to the fact that you should really just have one table.

I went in to a tiny more detail in the other post:
I was thinking like it was for Repeating Fields, where one could have 6
different values in a Repeating Field for say phone numbers, instead of having
6 separate phone fields.
I did this in FMP3, which was a way to keep all the phone numbers in one field,
with 6 different labels for them so one record would have all of the info. Now
when I went into fmp5/6 I changed it to separate fields, that were related by
the student ID. Back in the day, it was very haphazard and sloppy.

Ultimately, my question was it better to have 1 file w/ 4 tables, or 4 files
with one table in each.

Oh yeah, and "how to import whole tables into another file". Of which, I didn't
find any documentation on in the fmp 7 installation cd. Online, I saw that you
can do copy/paste tables in fmp9, but I haven't seen that for fmp 7. Official
FMP 7 documention seemed pretty skimpy. I just did a manual import, using the
Student database as the main one, and re-created all the other files as new
tables in the Student one.
--
"... respect, all good works are not done by only good folk. For here, at the
end of all things, we shall do what needs to be done."
--till next time, Jameson Stalanthas Yu -x- <<poetry.dolphins-cove.com>>

On Aug 14, 3:18 pm, ~consul <con... [at] INVALIDdolphins-cove.com> wrote:
> and thus Greg Dember inscribed ...
>
> > ~consul <con... [at] INVALIDdolphins-cove.com> wrote:
> > I suspect, from what you are saying here, that you have not
> > conceptualized your tables the best way. If so, what I am saying would
> > have been true in Filemaker 6 as much as it is true in 7/8/9.
> > Of course you can do it however you want, but the standard approach
> > would say you should only have one table (or file in FMP6) for all of
> > the data that you have mentioned.
> > It sounds like students, professors, Program Assitants and Community
> > Members are all PEOPLE.
>
> No, I guess the titles were misleading. Students are my students, about 1200 who
> are in my program. Professors really means the Course info, of which there may
> be about 80 profs working in 300 courses. Community are locations, and Program
> Assistants, are supervisors.
>
> Students was the main file (aka originally called 'Macrofile') and they would
> sign up for various Courses_Professors, be assigned to volunteer in various
> Communities, and be overseen by ProgramAssistants. The Student file then has a
> bunch of relationships b/w the files.
>
> I hope I was clearer this time. I think I was just being unclear as to what new
> thing I was trying to convey, when in reality, it was the same concepts as before.
>
> > I believe that you should just have all of your data in one table called
> > people, and then you can have a field that designates what kind of
> > person is represented in each record. There may be some fields that
> > are only relevant to certain kinds of people. If so, you can leave them
> > empty when appropriate.
> > What I am describing is the "correct" approach to database design. If
> > what you are doing is VERY simple than it may be more convenient for you
> > to keep it the way you've been doing it. But the question that you
> > asked, about wanting to "share" the email field among the different
> > tables points to the fact that you should really just have one table.
>
> I went in to a tiny more detail in the other post:
> I was thinking like it was for Repeating Fields, where one could have 6
> different values in a Repeating Field for say phone numbers, instead of having
> 6 separate phone fields.
> I did this in FMP3, which was a way to keep all the phone numbers in one field,
> with 6 different labels for them so one record would have all of the info. Now
> when I went into fmp5/6 I changed it to separate fields, that were related by
> the student ID. Back in the day, it was very haphazard and sloppy.
>
> Ultimately, my question was it better to have 1 file w/ 4 tables, or 4 files
> with one table in each.
>
> Oh yeah, and "how to import whole tables into another file". Of which, I didn't
> find any documentation on in the fmp 7 installation cd. Online, I saw that you
> can do copy/paste tables in fmp9, but I haven't seen that for fmp 7. Official
> FMP 7 documention seemed pretty skimpy. I just did a manual import, using the
> Student database as the main one, and re-created all the other files as new
> tables in the Student one.
> --
> "... respect, all good works are not done by only good folk. For here, at the
> end of all things, we shall do what needs to be done."
> --till next time, Jameson Stalanthas Yu -x- <<poetry.dolphins-cove.com>>

FM7 gives you the ability to put more than 1 table in a file. You
should use it. There are reasons to separate data and interface, but
you've got to get the basic concepts of relational design first.

You should listen to the advice that's been given twice; you're being
pretty well understood. Your database structure is terribly un-
optimized. You should have one table for People, in which you have a
field of Type that specifies Student, Program Assistant, Professor.
You'll still need a table for Communitues and Courses, but instead of
capturing the Professor info in a Course table, you'll simply link a
Course to a Professor.

Forget about the actual data being duplicated, that's not what we're
talking about, we're talking about the duplication of the types of
data (First Name, Last Name, Phone Number, E-mail Address, SSN, etc
etc). You want to eliminate that duplication.

G

and thus Grip inscribed ...
> On Aug 14, 3:18 pm, ~consul <con... [at] INVALIDdolphins-cove.com> wrote:
>> No, I guess the titles were misleading. Students are my students, about 1200 who
>> are in my program. Professors really means the Course info, of which there may
>> be about 80 profs working in 300 courses. Community are locations, and Program
>> Assistants, are supervisors.
>> Students was the main file (aka originally called 'Macrofile') and they would
>> sign up for various Courses_Professors, be assigned to volunteer in various
>> Communities, and be overseen by ProgramAssistants. The Student file then has a
>> bunch of relationships b/w the files.
> You should listen to the advice that's been given twice; you're being
> pretty well understood. Your database structure is terribly un-
> optimized. You should have one table for People, in which you have a
> field of Type that specifies Student, Program Assistant, Professor.
> You'll still need a table for Communitues and Courses, but instead of
> capturing the Professor info in a Course table, you'll simply link a
> Course to a Professor.

I suppose I can have a separate script button for each new input for Student,
ProgramAssistant, and Professor that would automatically fill in the field Type.

Having them in separate files at first made sense as I was also having different
folks logging in online to different databases. I could give Professors full
acccess to the Course_Professor database, and not the other ones in Web
Security. But if I combine them, then I have give full access to one part, and
restrict other parts.

> Forget about the actual data being duplicated, that's not what we're
> talking about, we're talking about the duplication of the types of
> data (First Name, Last Name, Phone Number, E-mail Address, SSN, etc
> etc). You want to eliminate that duplication.

I understood that. I was just shying away from the need of a "Type" label for
every new record.
--
"... respect, all good works are not done by only good folk. For here, at the
end of all things, we shall do what needs to be done."
--till next time, Jameson Stalanthas Yu -x- <<poetry.dolphins-cove.com>>

In article <fa1l36$8b0$1 [at] gist.usc.edu>,
~consul <consul [at] INVALIDdolphins-cove.com> wrote:

> and thus Grip inscribed ...
> > On Aug 14, 3:18 pm, ~consul <con... [at] INVALIDdolphins-cove.com> wrote:
> >> No, I guess the titles were misleading. Students are my students, about
> >> 1200 who
> >> are in my program. Professors really means the Course info, of which there
> >> may
> >> be about 80 profs working in 300 courses. Community are locations, and
> >> Program
> >> Assistants, are supervisors.
> >> Students was the main file (aka originally called 'Macrofile') and they
> >> would
> >> sign up for various Courses_Professors, be assigned to volunteer in
> >> various
> >> Communities, and be overseen by ProgramAssistants. The Student file then
> >> has a
> >> bunch of relationships b/w the files.
> > You should listen to the advice that's been given twice; you're being
> > pretty well understood. Your database structure is terribly un-
> > optimized. You should have one table for People, in which you have a
> > field of Type that specifies Student, Program Assistant, Professor.
> > You'll still need a table for Communitues and Courses, but instead of
> > capturing the Professor info in a Course table, you'll simply link a
> > Course to a Professor.
>
> I suppose I can have a separate script button for each new input for Student,
> ProgramAssistant, and Professor that would automatically fill in the field
> Type.
>
> Having them in separate files at first made sense as I was also having
> different
> folks logging in online to different databases. I could give Professors full
> acccess to the Course_Professor database, and not the other ones in Web
> Security. But if I combine them, then I have give full access to one part,
> and
> restrict other parts.
>
> > Forget about the actual data being duplicated, that's not what we're
> > talking about, we're talking about the duplication of the types of
> > data (First Name, Last Name, Phone Number, E-mail Address, SSN, etc
> > etc). You want to eliminate that duplication.
>
> I understood that. I was just shying away from the need of a "Type" label for
> every new record.

Did you really mean "Full access?" If so, that is a very bad idea. That
allows users to change the database design, which would be disastrous.
If they can do it, somebody eventually will.

Perhaps you meant read and write access, which makes sense, the ability
to create, edit and perhaps delete records.

You can set that up on a table-by-table, record-by record,
field-by-field, layout-by-layout basis in Accounts and Privileges.
Accounts and Privileges is your friend. Use it.

Also, you should limit the ability to delete records. If you have
standard menu commands available to the users, somebody could delete all
the records. It is an easy mistake to make; I have done it myself. So
you should definitely limit the menu commands available to users, if you
have not already done so. You can set up limited menu availability in
Accounts and Privileges. In Filemaker Advanced (formerly developer) you
can set up custom menu sets, which you might want to consider.

Beware also of making available the menu command to create new records.
With this, people can create "child" records directly which will have no
connection to a parent record. Allow creation of records directly only
in the Parent records, and of Child records only from the Parent
records, preferably by a script called by a pushbutton on the Parent
record.

--
For email, change <fake> to <earthlink>
Bill Collins

On 2007-08-16 06:56:34 -0700, ~consul <consul [at] INVALIDdolphins-cove.com> said:

> I suppose I can have a separate script button for each new input for
> Student, ProgramAssistant, and Professor that would automatically fill
> in the field Type.
>
> Having them in separate files at first made sense as I was also having
> different folks logging in online to different databases. I could give
> Professors full acccess to the Course_Professor database, and not the
> other ones in Web Security. But if I combine them, then I have give
> full access to one part, and restrict other parts.

With FM 8 or 9, you can give each different type of person a separate
interface file, but keep all data in one table.

Or, you could keep the ENTRY tables separate, and then combine them
later for reporting/mailings or other admin activity.
--
Lynn Allen
--
www.semiotics.com
562.938.7890
FSA Associate
Long Beach, CA

and thus Bill inscribed ...
> ~consul <consul [at] INVALIDdolphins-cove.com> wrote:
> Did you really mean "Full access?" If so, that is a very bad idea. That
> allows users to change the database design, which would be disastrous.
> If they can do it, somebody eventually will.
> Perhaps you meant read and write access, which makes sense, the ability
> to create, edit and perhaps delete records.

Sorry, read/write. Let them work on their records info, look at what other
professors do. No deleting or creating. Sorry, late night, didn't mean to be
imprecise.

> You can set that up on a table-by-table, record-by record,
> field-by-field, layout-by-layout basis in Accounts and Privileges.
> Accounts and Privileges is your friend. Use it.

I haven't had time to play around with it yet on fmp 7, but I will next week.
I've been on "vacation" for the past week. Though I gotta get my usenet fix. :)

Thanks for the other tips, I will keep them in mind.

--
"... respect, all good works are not done by only good folk. For here, at the
end of all things, we shall do what needs to be done."
--till next time, Jameson Stalanthas Yu -x- <<poetry.dolphins-cove.com>>