If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Separate tables: use duplicate attribute names or not?

I'm fairly new to database design, and I'd like some advice on which is best: allow attributes in separate tables to have the same name, or make all attribute names unique.

For example, if I have two tables, one for teachers and one for students, and all I care about is their zip code, should I just use "zip_code" as the attribute name, or should I use "teacher_zip_code" and "student_zip_code"? Along the same lines, what about a "name" field for both a table for dogs and another table for companies, or should I use "dog_name" and "company_name"?

I've done a bit of reading on database design and some places seem to say one way, while another will say the other way. I've gone back and forth myself, so I thought I'd ask for a little guidance from those more knowledgeable than myself. Thanks.

Scrolling up a couple lines from that link says: "Some people think that my habit of including the table name inside a field name (as in CUSTOMER.CUSTOMER_ID) introduces a level of redundancy and is therefore wrong. I consider this view to be too narrow as it does not cater for all the different circumstances I have encountered over the years."

He does make some good points on that page, but the redundancy is tough for me to accept. If I can't figure out that customer.name is the name of a customer, calling it customer.customer_name probably won't help much...

That does sound like a bit too much redundancy, but consider that you may have occasion to want to know if you're picking up the Customer Id from the Customer file or from an Invoice file when you're reporting Accounts Recievable transactions.

Real world scenario, you can have Accounts Receivable transactions that have a source invoice, and therefore pick up the Customer Id from the Invoice or you can have "immediate" AR transactions that never had an Invoice created and where the user entered the Customer Id instead of an Invoice number.

Scrolling up a couple lines from that link says: "Some people think that my habit of including the table name inside a field name (as in CUSTOMER.CUSTOMER_ID) introduces a level of redundancy and is therefore wrong. I consider this view to be too narrow as it does not cater for all the different circumstances I have encountered over the years."

his point carries some weight in that you cannot use just ID everywhere, specificallyt when it comes to needing to distinguish between primary keys and foreign keys to other tables in the same table (they cannot both be called ID in the same table)

but this is not carte blanche to adopt the practice elsewhere in other columns where it is not necessary

he says "A field named ID simply says that it contains an identity, but the identity of what? A field named DESCRIPTION simply says that it contains a description, but the description of what?"

the answers are drop dead simple: the column (tony calls it a "field" but i prefer the more correct "column") named ID is the identity of the entity stored in that particular table, and the DESCRIPTION is the description of the entity stored in that table

that's not so hard, is it?

if you run a query against the Companies table, then the id is the company id and the description is the company description

okay, what happens if you have a join query? then you use column aliases --

Code:

select parolees.name as parolee_name
, parole_officers.name as parole_officer_name
from ...

now some folks will argue that if you create a view of the table, using the necessary column aliases, then the view effectively will have these column names, i.e. with prefixes embedded, but i don't see how this gives permission to hard-code the prefixes into the base column names

Originally Posted by jrwahl

He does make some good points on that page, but the redundancy is tough for me to accept. If I can't figure out that customer.name is the name of a customer, calling it customer.customer_name probably won't help much...

please get me a list of those places which advocate putting the table name as a prefix into the column name, for they are wrong and i want to add them to my knuckleheads file

go back and forth no longer

You wouldn't want to work where I do. The standard here is to include an abbreviated version of the table name, and all ancestor table names in the column names so you get names like LOC_CODE, DPT_LOC_CODE, MGR_DPT_LOC_CODE, EMP_MGR_DPT_LOC_CODE, ...

Nice

The justification is apparently that it makes it possible to search application code for usages of a particular column since column names are unique across the whole database.

Personally, I prefer a column to have the same name wherever it appears, modified where required to preserve uniqueness. So CUSTOMER.CUSTOMER_ID and ORDER.CUSTOMER_ID, and EMPLOYEE.EMPLOYEE_ID and EMPLOYEE.MANAGER_EMPLOYEE_ID.

You wouldn't want to work where I do. The standard here is to include an abbreviated version of the table name, and all ancestor table names in the column names so you get names like LOC_CODE, DPT_LOC_CODE, MGR_DPT_LOC_CODE, EMP_MGR_DPT_LOC_CODE, ...

Well, it's an Oracle database so it is limited to 30 character column names, which means the rule has to be broken once the chain gets really long. The "best" I can find has 5 ancestors like AAA_BBB_CCC_DDD_EEE_FFF_REFNO.

Ah, because then it's easier to tell if you're dealing with a Date or Amount field.

I work with PICK database type systems, where you can list your dictionary (fields) separately from viewing the whole database. If you can look at a field and know if it's PURCHASE_DT or PURCHASE_AMT, you can then decide which one you want to include in a report. However if it isn't included in the Dictionary (field) name, then you have to include another column whenever you do a "SORT DICT filename" to show the type: "SORT DICT filename *A7". It saves a few keystrokes and it's easier for users to remember the first statement.