relationship window

[damsel]

I have two newbie questions. Your help is greatly appreciated

1. How do I orgazine all the tables in the relationship window ?
My tables are cluttered all over the place that I couldn't even see some of em anymore.

2. I would like to make a report just like this. All of our customers are below average income.

Customer ID Is Cust. below average Income?
ABCDG N
FGASD N
ASDFD N

Is it a good relational table if I insert a field of "Is Cust. below average income" in the Customer Table and has it N as the default value ?

Thank you very much

 

[Shane_Mc]

First of all, There are no rules with regard to organising tables in your relationship window. Just sort them out so you can see them as best as possible. But if there are so many that it becomes really difficult do ask yourself, Do I need them all? You shouldn't be breaking any of the rules of normalisation. If you aren't fully clear on these Pat posted a good chapter on them a few days ago.

Secondly, I see no fault with the field you have suggested "Is cust. below average income?" Except for

1 Leave out the spaces and ".", oh and the "?" if it is in it (It is good practice not to have spaces in any field names, variable names etc. when programming).
2 Try to make it shorter. Believe me it will make sense when you are trying to squeze a load of tables into your relationship diagram and want to see your field names fully aswell.

 

[cogent1]

1. you can drag those little boxes aroiund in the relationship window and put 'em where you want. If there are lots of the critters, reduce their size. You only want to see the join fields usually.

2. You can have a YES/NO field in your Customer Table and set it to default to NO. There are no relational issues. One customer has one income and that is a unique attribute. Do yourself a favor and give it a short name with no spaces or non-alphanumeric characters. BelowAverageIncome would be suitable,

 

[Pat Hartman]

Is it a good relational table if I insert a field of "Is Cust. below average income" in the Customer Table and has it N as the default value ?

No. The Y/N value that you want to store in this field is static and represents a status at a point in time. If either the person's income or the income standard is changed, that would invalidate the flag's value.

 

[cogent1]

Yes, but that holds true for any limited choice field. A simple update query can cope with most changes. Meanwhile the value is an adequate generalization.

 

[Pat Hartman]

No, it doesn't hold for any limited choice field. I am assuming that both the customers’ income level and some indication of what amount is considered an average income are available. When information from which a field can be calculated is stored in the same table or as constants, it is wrong to store the calculated value. If the customer's income level is not stored, then a flag of this type is an adequate substitute. Both the flag and the income level are time dependent and their accurate setting is not possible in any automatic manner. If the customer's status/income changes, someone would need to inform the data entry people to make an appropriate change. So, the bottom line is, if you store the income, keeping a flag is redundant and a violation of second normal form.

 

[cogent1]

Yes, BUT there is nothing to indicate that the the other income information referred to is stored elsewhere. If it is not, then this is a valid, if rather imprecise, measure of a client's status and can be used as a general indicator of prosperity. As we don't know the purposes for which this field is to be used, we can only speculate on whether it needs to be tied down to a more precise yardstick.

As to this representing a status at a given point in time, that surely applies to any field that might be updated in a database. A user's name, marital status, title,even sex, are subject to changes which necessitate manual alterations to a database.

 

[Rich]

Wouldn't financial status stored as a simple flag be a meaningless and subjective statistic anyway?
Marital status etc for most, excepting perhaps Holywood Stars/ Starlets remains mostly consistent.

 

[cogent1]

If you were doing a demographic survey and had some fixed criterion for wealth/poverty, a yes/no would be objective within the terms of the survey. I can be married today(God forbid!) and divorced tomorrow. I can be rich today and poor tomorrow. Where's the difference as far as the database is concerned? Both states require the database to be updated.

There is no disagreement about the broad principles, but the initiator of this thread has not told us what additional information is held and we don't know why the data is needed. From the general tone of the original question, I am inclined to think that the user is looking for a general indicator rather than a wholly objective measure. I may be wrong. Different assumptions have been made by different respondents. Both are valid.

 

[Rich]

Will it be a safe assumption to flag you as single for the foreseeable future then?

 

[cogent1]

Yes (but don't tell Annie!)