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.

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

I originally found the suggestion for this here:

http://www.tonymarston.net/php-mysql...es.and.content

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.

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 --
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


i agree with you completely here

this only happens in applications which let it happen

in properly designed applications, this would be disallowed by relational integrity

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.

He he - what is the longest chain of ancestors in the database?

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.

I recently did some work on a database whose designers followed a very strict 8 character table name convention.

...4 of those characters were used to display the company acronym and an underscore

ABC_emps, ABC_orde, ASC_odet, etc...

Yikes, georgev, I would HATE that. I like nice descriptive names, but I do try to keep them short:

PURCH_DT, INVOICE_DT, DATE_DUE, AMT_DUE, PAID_DT, PAID_AMT

4 usable characters would change it to PDT, IDT, DUDT, AMDU, PDDT, PAMT

awful.

to extend the conversation into related areas...

what is it with this penchant for embedding the datatype into the column name?

why PURCHASE_DATE instead of just PURCHASE? if we were consistent, we would say USERNAME_STRING instead of USERNAME

Aha - are you trying to start a fight with blindman?

George - I just want to get this straight - 8 char names and the first four of every name is the same? And it indicates the company?

Oh sorry - no they are not all the same. Oops.

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.