Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Jan 2008
    Posts
    10

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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2008
    Posts
    10
    Quote Originally Posted by r937
    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...

  4. #4
    Join Date
    Aug 2003
    Location
    Toronto, Ontario, Canada
    Posts
    203
    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.
    When it rains, it pours.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jrwahl
    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


    Quote 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...
    i agree with you completely here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rockingred
    ... and where the user entered the Customer Id instead of an Invoice number.
    this only happens in applications which let it happen

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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by r937
    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.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by andrewst
    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
    He he - what is the longest chain of ancestors in the database?

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by pootle flump
    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.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    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...
    George
    Home | Blog

  11. #11
    Join Date
    Aug 2003
    Location
    Toronto, Ontario, Canada
    Posts
    203
    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.
    When it rains, it pours.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh sorry - no they are not all the same. Oops.

  15. #15
    Join Date
    Aug 2003
    Location
    Toronto, Ontario, Canada
    Posts
    203
    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.
    When it rains, it pours.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •