Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Mar 2008
    Posts
    33

    Unanswered: 2 tables with the same name... should I?

    Hi all,

    I am debating if I should go ahead with my instincts which are telling me that I shouldn't name 2 tables with the same name... or if I should go ahead and name more then one table the same name as long as they are in different schema...

    something like (making stuff up here for sake of showing an example...)

    car.Reservations
    hotel.Reservations
    flights.Reservations

    or

    audit.TransactionTypes
    inventory.TransactionTypes
    sales.TransactionTypes


    etc...


    should I go this way or use something more traditional like dbo.SALES_TransactionTypes or dbo.SalesTransactionTypes?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    go for it!!!

    if you want to name your tables that way, i say go for it!!!1!!!!

    i mean, what could happen?

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

  3. #3
    Join Date
    Mar 2008
    Posts
    33
    that's the spirit!

    anyone knows any reason why having more then one table with the same name could be a problem? (FYI I am designing this system from scratch so I don't have to fear problems such as badly written legacy applications that don't support two part naming or something ugly of the kind...)

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Main reason against would be the confusion, in case you hand support of this database to another person. Have you considered a reservations table with a reservation_type field to differentiate the records?

  5. #5
    Join Date
    Mar 2008
    Posts
    33
    confusion -> agree...

    as for "reservations table with a reservation_type field", the example above was just a simplified way of explaining what I meant. In reality the different tables I have to deal with will have significantly different structure and if I was to create table with a "type" field I would have to include many columns that would be used only for some types and not for others. In fact at least half of my columns are different depending on the type...

    I guess if both tables are so different from one another I should perhaps just come up with a distinct name for each one... but it's so tempting to call a table 'Transactions' lol...

  6. #6
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Quote Originally Posted by r937
    go for it!!!

    if you want to name your tables that way, i say go for it!!!1!!!!

    i mean, what could happen?

    WTF...........................
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't do it. That is not the way schemas were meant to be used.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    ...but schemas do work that way!

    -PatP

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, it will work. No, it is not a substitute for good relational design.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Mar 2008
    Posts
    33
    it is not a substitute for good relational design.

    To be clear my tables have really different purpose and structure, I am not considering schema as a substitute for good relational design but rather I am considering them as a substitute for the PREFIX_ that we often see in table naming... i.e. hr.Payrolls would be used instead of dbo.HR_Payrolls.

    however like mentioned earlier in some case by removing the prefix_ you end up with 2 tables with the same name...

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    those aren't prefixes, those are owners
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2008
    Posts
    1

    Wink Yes !!!

    In my opinion you're correct doing that...
    Don't matter if it's owners...

    Let's think about performance !!!
    What is faster, search registries in a table with 100 lines or in a table with 1000000 ???

    Another point is, think about OO, what is easier to do, change in your class the place that you need to look, or have a user that corresponds to your object ?? He can also use a user for each class !

    You must do it...

    But keep in mind...
    To pay attention to the table that you're working during maintenance...
    To give right permission to each table, sp, etc....
    When calling a table use the full name [owner].[table_name]

    Good Luck..
    Last edited by zopostyle; 03-15-08 at 16:55.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Those designate schemas/owners, which have implications beyond a simple naming convention. Different schemas need different security plans, for instance.
    If you want to categorize your tables by name, just use an underscore.
    If you want to look like an idiot to the next dba that sees your database, use different schemas.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Actually this makes the schemas behave somewhat like an Oracle tablespace. If you decide to do this, I'd recomment a separate file for objects of each schema, which will increase the similarity.

    There are both costs and benefits of what you are proposing. First and foremost, many applications don't support two part naming well, and this could be a serious problem for you in the future. The benefits come in the form of a more OOP-like environment, which implies a higher probability of code re-use. You'll have to examine the costs and the benefits of using this feature to determine what is the correct answer for your situation.

    -PatP

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by Pat Phelan
    Actually this makes the schemas behave somewhat like an Oracle tablespace.
    Actually, Oracle's tablespaces are more like MSSQL's Filegroups. They both have distinct sets of files, and objects can be put in them, in order to separate I/O operations.

    The two concept of Schemas (Schemae?) are now much more alike in SQL 2005 and Oracle. SQL 2000 still has the concept of an object owner, but that is dissolving in SQL 2005.

Posting Permissions

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