help with relationships

[ntambomvu]

Hello Chaps,
I am watching a tutorial on relationships which shows how to create relationships between tables
The process seems to be quite cumbersome and I would like your input on the proposed system


which i am thinking of using
the table is a list of customers.
one of the fields is obviously the customername.
In the formatting of the customer name I format according to preset formatting rules which
capitalizes the name ,removes all punctuation, places spaces in place of full stops where initials
may have been entered with fullstops between initials and quite a number of other rules so that the
computer sees all names with the same format.
I then create a reference to each and every customer which uniquely identifies that particular customer.
I then use this customer reference as the identifier in all following tables.
so to explain
(each table below will also have many more fields than shown)
customers table contains customername and custref
streetaddress table contains custref and the street address
postaddress table contains custref and postal address detail
accounts table contains custref and account payment record

Is this a logical and functional way of keeping relationships and what would be the pitfalls?

Thanks for all advice

redards fred

[CJ_London]

Some quick comments:

I would have one table for addresses, not two.
Custref in the customer table would normally be an autonumber rather than a created value - the family key in the other tables would then be long
If you need custref as a value (such as account reference) just keep in a separate field in the customer table, it would not need to be in the other tables (you would use the family key to link instead)
address only needs to be in another table if a customer can have more than one address
suspect your accountpayment table is likely to require more than one table but not enough info to advise
formatting of a customer name is often a good idea but consider splitting the name into some component parts. Depends if the customer is a company or individual - company names usually go into one field and better to format per the customer description - you wouldn't want BBC to be Bbc for example. For individuals better to split to title, first name, last name - makes them easier to find

[Micron]

Curious to know why the addresses wouldn't just be in tblCustomers since their address is an attribute of the customer entity? Even if there were multiple customers at the same address as in some non-commercial type of applications? Agree that the autonumber is a valid approach. Maybe OP could use some pre-emptive warnings on their use though:

http://www.utteraccess.com/wiki/Autonumbers
I would consider a CustName field holding the unmodified version so you could always know the original, and a NameAlias field for the modified one where the minimum change would be to remove apostrophes (OGrady rather than O'Grady) because they often cause issues. This assumes there are other possible significant problem characters but has nothing to do with modifying Mr. J. Smith to Mr J Smith because I would also put salutation (gender/marital based or not) and first and last names in separate fields. Some countries have much different salutations than western countries, so splitting them is the best approach IMHO.

[ntambomvu]

thank you for your valued response !!
1. yes i see what you mean about the customer name and addresses being in the same table and I will change the table accordingly.
2. sorry but i do not understand what you mean by "family key" would you please give me an example?
3. I would like to have custref as a formatted entry because it is formated in a manner which lets me easily see the
name of the customer instead of a number when debugging . Other tables like product movement in the inventory table will be used to
record product movement by customer. In work in progress table the curef will enable quick identification of who a work ticket is for etc.\
does this make sense?? please give me your opinion

[ntambomvu]

You and Ajax have shown me the error in customer table and that I should only have one table with all addresses ets- so i will
change the table accordingly.
I dont know what "OP" in first paragraph stands for -- please explain
When you use the term name alias am I correct in assuming that that would be the same thing as I am
calling "custref" ??- But i do agree that my name formatting does need some careful consideration-
( the use of "so" is just a South African corruption of a phrase like " to further elaborate" - excuse the local slang)

thanks for the valued input and your further input will be muck appreciated.
Imediately hereafter i will be following the link that you are sending me to

Regards
Fred from South Africa (where we are enjoying a lovely hot summer)

[ntambomvu]

hello micron,
your link to wiki/autonumbers refers
interesting stuff-
I used to program in dbase 4.2 so the autonumbering option is something that I am glad to know more about.
am I right in assuming that me as a programmer would be better off never to use a column which is autonumbered
in the code because it might create problems when records are deleted?? It looks like it would be better just to
forget that autonumber exists and let access only use it for the keyfield in each table ??
Thereafter i just let access do what it likes with autonumbered columns and I keep away from them?
In dBase the only time i may have been tempted to use something like autonumber would have been when
I wanted to display the last record entered .
the table was only "weeded" out when it was nessessary to reduce the size of the table - but this was
never nessessary in my small applications- any deleted recorded were just flagged by dBase as "deleted"
but still remained in the table until the tabled was "packed"
We used to call this the natural order and i dont think i ever used it because i always would use an index
applicable to what i was looking for.
So I will just keep my nose out of autonumber and let Access do with it what it likes ??
OK ?

thanks
Fred

[Micron]

I dont know what "OP" in first paragraph stands for -- please explain

Original Poster (person who started the thread)

When you use the term name alias am I correct in assuming that that would be the same thing as I am
calling "custref" ??

I'm advocating keeping the original valid name in its own field. Then break it into additional fields as you see fit. I would consider (in addition to any other fields that come before and after) as an example:

Salutation	Fname	Lname	Alias
Mr.	Ian	O'Hara	Ohara

You may never encounter the issue that arises when trying to build query sql in code and the names have apostrophes, but the potential is there. If you send email, you'd use the LName field in the To, not the alias as Mr. O'Hara might object to OHara. The alias might be going a bit beyond what you need/want. As a minimum, have the 1st 3 columns, but not Mr Ian OHara all in one field.

(the use of "so" is just a South African corruption of a phrase like " to further elaborate" - excuse the local slang)

That's just a social commentary in my signature. It appears in every post unless I remove it, which I usually do after the first post I make. It may not be obvious, but it is also a commentary on "Like" for which I don't seem to have the words to truly express how much that one bugs the crap out of me. I will have to come up with one for "Uh and Umm" as they're getting far too prevalent on TV. If you were here a couple of months ago, you would have seen "Irregardless, ain't no such word as reoccur". I was called out for one other error for that one, yet there were at least 2 others, all deliberate. I will address your other questions in a separate post as soon as I get a chance. Back to my chores for the moment.

[CJ_London]

sorry but i do not understand what you mean by "family key" would you please give me an example?

family key is also known as a foreign key and often abbreviated to FK. Every table has a Primary Key (or Parent Key) and often abbreviated to PK. A PK uniquely identifies a record and that is its only purpose. Other tables that are related to that table - i.e. the children contain a FK which identifies the parent. This is how you map relationships

simple example

tblCustomers
CustomerPK autonumber
CustomerName text

tblAccounts
AccountPK autonumber
CustomerFK long
AccountName text

tblCustomers
PK...Name
1.....Fred
2.....George
3.....Harry

tblAccounts
PK...FK....Name
1....2...….current
2....2......savings
3....1......current


George has 2 accounts, Harry has 1 and Fred has none

[Micron]

a programmer would be better off never to use a column which is autonumbered in the code because it might create problems when records are deleted?

True, you would not explicitly use any value in that field, but it is common to refer to the field itself as in "do something about a record where the ID (autonumber) = some control on my form" but obviously not in those words. Basically they guarantee that each record has a unique identifier and in fact, not every db will use them. A simple text field can provide a unique ID provided it is set up correctly and in fact, one huge ODBC database I worked in for years was set up this way.

the only time i may have been tempted to use something like autonumber would have been when I wanted to display the last record entered.

That would be another decent example of its use. Using MAX on a text field wouldn't work. While I have read that it's possible to generate negative autonumbers I suspect it's a rare thing and I've never experience it. That would indicate there's a problem going on.

More research into autonumber fields may be warranted on your part, just to provide more clarity for you.

[CJ_London]

the only time i may have been tempted to use something like autonumber would have been when I wanted to display the last record entered.

Although it rarely fails if the autonumber has been set to increment that the latest record has the largest autonumber, it is not guaranteed - particularly in a multi user environment. You might find this link of interest http://www.utteraccess.com/wiki/Autonumbers, particularly the last section. EDIT - Sorry, just realised Micron has already provided the link

re negative numbers, set the new values property to random rather than increment and you can easily get negative numbers. random has its benefits on occasion.

You can use text as a unique ID e.g. for things like US states, airport codes and the like - short codes and a limited number of records. But be aware that even a 3 character text field takes 13 bytes against 4 bytes for an autonumber - so your db will require more space for both the field and the index and the indexing performance will be roughly three times slower with text.

[June7]

I also advocate separate fields for name parts of individuals. It is easier to concatenate fields than to split up a string. Complication does arise if the customer base is mixing businesses and individuals. Careful about forcing capitalization. Some names might not use capitals for some of their parts or it might not be clear if they should be. Access is not case sensitive by default so doesn't matter for search/sort.

Ex: Otto Martin von der Gablentz, David de Gea Quintana

adidas logo is all lower case and it is all lower case on their LinkedIn page.

[ntambomvu]

+my sincere thanks to you both. I have a small company and am not working in the company of
other programmers - so I appreciate your helping me. ( I envy programmers who are part of a team
I would love to have the advantage of looking over my shoulder and saying to someone" how do you
do this and that")

[ntambomvu]

about the social commentary-I must say i also get annoyed at the indiscriminate use of
unconnected or inappropriate words in the modern slang and jargon seen on TV
I sometimes watch a station called "Fuel" - love the surfing,skiing and snowboarding -
these people are truly dedicated athletes- But Please dont let thm open their mouths-
sometimes I wonder if they were ever taught English?

[Micron]

Like, dude, I'm so, like, with you, like 110 percent, like.

How ridiculous is that? Alas, it's not far from reality. I live in a world where they don't even teach cursive writing anymore. I guess X will become the defacto standard for signatures before long.

[June7]

So, Dude, defacto is really like, ya know, 2 words: like de facto! It's like, Latin, meaning 'in fact', ya know?

Yes, cursive is a lost art in U.S. I remember disappointment when I got a C for penmanship in 3rd grade. I had two 2 semesters of Russian 14 years ago and instructor said cursive was used for everything, even filling out forms, never print characters. Maybe that's changed there as well by now.