Prefixes - but at what cost!?



  • Another dev in my team likes to prefix all his DB columns with 3 letters - he says this is so that he can easily see which tables join to which other tables when writing queries

    The issue I've got is that the 3 letter prefixes don't really mean anything and that you soon run out of 3 letter prefixes when you have a DB with a handful of tables, then they start making even less sense.

    For instance, a prefix for a file types table called FileTypes might be 'FIL' - so all the fields will be as so:

    FIL_Type

    FIL_Name

    etc

    Of course as soon as you add another table called Files you get the issue of 'FIL' being taken, and so another seemingly random prefix is chosen... it might be 'FUL' or 'FLE' or something equally unrelated - personally I don't think it helps anyone and just adds confusion as it also carries over to our business objects (as they usually take the column names as their property names)

    The latest confusion resulted from me using a 'files' business object in an unfamiliar project and realising most of it's fields are called PDF_XXXX - for a second I thought that maybe this object only deals with PDF files - nope. It turns out that because the first two letters of the application form the acronym P.D. and the table was a files table, the logical prefix was PDF!

    We have SQL2008 which already has an intellisense type thing going on, and if were working in SQL2005 Managment Studio we use SQL Prompt which gives you intellisense functionality - so I can't see where writing queries can be a pain - most of the sprocs we use are simple generated CRUD ops anyway. Also, if you aren't sure about a FK just look at the table structure! If you give your columns good names without prefixes it's less typing and joins become quite apparent

    What are the opinions on this one? Anyone else have a similar compulsion, or know anyone with a similar compulsion?


  • Discourse touched me in a no-no place

    @Charleh said:

    he says this is so that he can easily see which tables join to which other tables when writing queries



    [...]

    What are the opinions on this one?
    He's an idiot. Replace him. This is on a par with prefixing all tables with tbl, views wit vw and fields with fld.



  • @Charleh said:

    Another dev in my team likes to prefix all his DB columns with 3 letters - he says this is so that he can easily see which tables join to which other tables when writing queries
    A 3-letter prefix is too limited! You should use UUID prefixes!

     

    Or, you know, if identifying which column belongs to which table is that much of a problem, you can precede every column name with its respective table name.

    AKA, being sane.



  • The db on my current project has this, courtesy of the initial developer. It seems to work reasonably well - with 40-odd tables, almost all the acronyms do still make some kind of sense. Doesn't mean I like it though! I'm still hoping for the day when I can rip out the db and replace it something that removes the little insanities...



  • @Zecc said:

    A 3-letter prefix is too limited! You should use UUID prefixes!

    I don't like to differentiate between table and field names, I like to name all tables and fields dat00001, dat00002, dat00003 etc to avoid any confusion.  Then the entire structure of the db can be described as a nested set!

     


  • Trolleybus Mechanic

     One of the project I'm working on has a schema like this-- except that the three letters are just an abbreviation of the table name itself-- and most table name, for some reason, starts with the same prefix of "e_"

    So that's actually only two letter of the table name to use.

    And to make things better, the prefix sometimes also contains the first letter of the project type.  "ei_" for-- I have no clue what the "i" is for, since it's a scheduling project.

    Lots of fun when I had to create an Appointment and Assignment table for the project.

    "OK, Appointment will be ei_appointment, and every column will be  eia_*..." {click click type}

    "And Assignment will be ei_assignment and every column will be eia... ah, fuck."  {sound of my throwing the "3 letter" rule out the window, since no one looks at the DB anyways, and going eiasg_*}

     

     



  • @PJH said:

    This is on a par with prefixing all tables with tbl, views wit vw and fields with fld.

    On our project, we append _v to the end of all view names.  Just so you know, when you're looking at production code, that you're working with a view as opposed to a table.  It's important in a lot of my work personally.

    That said, you fell short.  Why not recompile the dbms so that VARCHAR is now fieldtype_VARCHAR?



  • @Charleh said:

    personally I don't think it helps anyone and just adds confusion as it also carries over to our business objects (as they usually take the column names as their property names)
     

    I see this also as a minor WTF, but considering what you have to start with, it's the least of your worries.

    My reason: The way you store data is not always the way you work with data, so why do your business object always have to look like your database?

    A minor example, You have a customer, with an address, your address are separated out because you have shipping addresses and billing addresses, you also have different type of customers, corporate and retail.

    So your table structure is Customer, AddressType, Address,  CustomerType

    Your business objects will be a Customer Object and an Address Object, you don't need address type as that is simply used in a join query to find the address you want, you also don't need a customer type object as it is simply pulled into your customer object as part of it's data.  You work with the data differently then how you save the data.  You don't display the customer type ID in the customer object, you display the description for the customer type.

    It is a simple explanation, with a few minor flaws due to its simplicity, but you get the point.  Your business object and data object combinations can and should abstract out any DB WTF's before you even get to the processing of that data.



  •  Or, you can go another way and replace the whole structure with a single table.

    Of course some of the fields would be empty... but then you can reuse them! Just give them generic names (fld_1, fld_2, fld_3 etc.) and depending on the context they could contain the customer  addresses, or the product color, or options on contract.

    The possibilities are endless!



  • @KattMan said:

    @Charleh said:

    personally I don't think it helps anyone and just adds confusion as it also carries over to our business objects (as they usually take the column names as their property names)
     

    I see this also as a minor WTF, but considering what you have to start with, it's the least of your worries.

    My reason: The way you store data is not always the way you work with data, so why do your business object always have to look like your database?

    A minor example, You have a customer, with an address, your address are separated out because you have shipping addresses and billing addresses, you also have different type of customers, corporate and retail.

    So your table structure is Customer, AddressType, Address,  CustomerType

    Your business objects will be a Customer Object and an Address Object, you don't need address type as that is simply used in a join query to find the address you want, you also don't need a customer type object as it is simply pulled into your customer object as part of it's data.  You work with the data differently then how you save the data.  You don't display the customer type ID in the customer object, you display the description for the customer type.

    It is a simple explanation, with a few minor flaws due to its simplicity, but you get the point.  Your business object and data object combinations can and should abstract out any DB WTF's before you even get to the processing of that data.

    We don't just mirror the tables into the object structure - some of it is abstracted out, but a lot of the time with code gen tools, you directly use a field from the database and your code gen generates a property which matches that field name. Why would you abstract a field if it didn't need abstracting? We're not talking about junction tables here, we're just talking about a single table->object relationship in most cases. Also if I didn't generate/create the object, how much say do I have over the property names defined on the object? Virtually none until I get my hands on it and realise everything is a WTF, then I have to refactor/regen or my head asplode trying to work with it.

    A field called SalesOrderID on the SalesOrders table will most likely be mapped to a SalesOrderID property on a SalesOrders object. At no point in my post did I say that the BO had to look identical to the structure, just that in a lot of objects, you don't need to do any abstracting - and if your DB structure is created from a sane perspective, then most field names lend themselves directly to property names on objects

    A field called SOD_SalesOrderID is pointless and when it sneaks it's way onto that SalesOrders BO you just get a headache seeing SOD_XXXX pop up in intellisense when you try to type someObject.SalesOrderID



  • @Zecc said:

    @Charleh said:

    Another dev in my team likes to prefix all his DB columns with 3 letters - he says this is so that he can easily see which tables join to which other tables when writing queries
    A 3-letter prefix is too limited! You should use UUID prefixes!

    Or, you know, if identifying which column belongs to which table is that much of a problem, you can precede every column name with its respective table name.

    AKA, being sane.

    this

    SELECT [foo list] From Users JOIN UserUploads as Files ON Files.user_id = Users.id ..........



  • that's nothing. Some 5 or 6 years ago I was employed by a guy who didn't know how to implement FK relationships in a relational DB. fair enough. so one evening I thought I'd successfully explained to him, only to discover the next day he'd somehow managed to implement the FKs in the PK table. as a comma-and-square-brackets-delimited value in a newly-created text column holding the ids of the related entries in the FK table. as in: "[2],[23],[666]" etc. he did also store anything date or time related as ticks meaning you had to convert to ticks when you queried and parse out to actually do anything useful with.



  • @JimLahey said:

    that's nothing. Some 5 or 6 years ago I was employed by a guy who didn't know how to implement FK relationships in a relational DB. fair enough. so one evening I thought I'd successfully explained to him, only to discover the next day he'd somehow managed to implement the FKs in the PK table. as a comma-and-square-brackets-delimited value in a newly-created text column holding the ids of the related entries in the FK table. as in: "[2],[23],[666]" etc. he did also store anything date or time related as ticks meaning you had to convert to ticks when you queried and parse out to actually do anything useful with.

    That sounds enterprisey. It probably took some serious technical knowhow to use/understand it - I'd have given him a medal...in the form of a crowbar to the face



  • @Charleh said:

    What are the opinions on this one? Anyone else have a similar compulsion, or know anyone with a similar compulsion?

    Teach the retard about table aliases.

    select f.type
    from FileTypes f
    where idiotCoworker = 1


  • I haven't done it in my own databases, but the large ERP system we use (with just over 500 tables, half of them empty) has 3 letter prefixes on every column, unique to each table, so that every column name is unique in the database. It saves them from having to prefix full table names or use table aliases in their SQL, as you would if you had identically named fields in two or more tables used in a query. And every primary key in the database is a guid, though many tables are clustered by natural or ordered alternate keys or by foreign keys.



  • @Kiss me I'm Polish said:

     Or, you can go another way and replace the whole structure with a single table.

    Of course some of the fields would be empty... but then you can reuse them! Just give them generic names (fld_1, fld_2, fld_3 etc.) and depending on the context they could contain the customer  addresses, or the product color, or options on contract.

    The possibilities are endless!

    The team I started at in this company did that... you had storedProc1, storedProc2, storedProc3, storedProc4, which held the names of stored procs that were used.  storedProc1 would be used in 5 different contexts, depending on which path it took through the code (i.e. it might have to do with fulfillment in one, or creating a product in another).  I moved off that team, but one of the employees that started working on it after I left told me how he was doing a project that used those, and put a value in storedProc1, and had them tell him "No, that kind of data goes in storedProc4."... As he said, "If you had a specific meaning for a row, why didn't you name it that?  You can name storedProc1 as fulfillmentProc and storedProc4 as f***TheNewbieProc".



  • In one company, we've used a similar scheme, but it just doesn't make sense. Example:

    select ord_number, cus_name
      from orders, customers
      where ord_customerid = cus_customerid
        and cus_code = 'ammoQ';
    

    Now the same, with table aliases instead of the prefix:

    select ord.number, cus.name
      from orders ord, customers cus
      where ord.customerid = cus.customerid
        and cus.code = 'ammoQ';
    

    As you can see, the table aliases allow me just as well, and without any doubt, and without writing much more code, to see which column is from which table. But I only need those aliases when I do a join - not for inserts, updates, deletes, single-table selects:

    select cus_name from customers where cus_code = 'ammoQ';
    update customers set cus_name='Erich' where cus_code='ammoQ';
    insert into customers(cus_name, cus_code) values ('Erich', 'ammoQ');
    delete from customers where cus_code = 'ammoQ';
    

    vs.

    select name from customers where code = 'ammoQ';
    update customers set name='Erich' where code='ammoQ';
    insert into customers(name, code) values ('Erich', 'ammoQ');
    delete from customers where code = 'ammoQ';
    

    Therefore, we have abandoned this practice.



  • @blakeyrat said:

    Teach the retard about table aliases.
    select f.type
    from FileTypes f
    where idiotCoworker = 1
    Win.

    Why did it take ~20 posts before somebody said this?  The worst that I do is prefix the table name of PKs and FKs, but that's mostly because "that's how I've always done it", and I do realize its redundant.



  • @C-Octothorpe said:

    Why did it take ~20 posts before somebody said this?

    Because people were busy saying how the person that wrote it is stupid?  We're not trying to educate the person who insists on doing it the wrong way.


  • @Sutherlands said:

    @C-Octothorpe said:

    Why did it take ~20 posts before somebody said this?

    Because people were busy saying how the person that wrote it is stupid?  We're not trying to educate the person who insists on doing it the wrong way.

    Obviously a post of such genius can't be written before the genius (me) arrives at the thread.



  • @ammoQ said:

    Therefore, we have abandoned this practice.

    Leaving the stupid version as the current practice?



  • @blakeyrat said:

    @Sutherlands said:

    @C-Octothorpe said:

    Why did it take ~20 posts before somebody said this?

    Because people were busy saying how the person that wrote it is stupid?  We're not trying to educate the person who insists on doing it the wrong way.
    Obviously a post of such genius can't be written before the genius (me) arrives at the thread.

    Um, genius?  According to your post it would place you *just* above a retard as a retard wouldn't know about table aliases...  And you do!  :)



  • @Qwerty said:

    @ammoQ said:

    Therefore, we have abandoned this practice.

    Leaving the stupid version as the current practice?

     

    Would that be so uncommon?



  • @blakeyrat said:

    @Sutherlands said:

    @C-Octothorpe said:

    Why did it take ~20 posts before somebody said this?

    Because people were busy saying how the person that wrote it is stupid?  We're not trying to educate the person who insists on doing it the wrong way.

    Obviously a post of such genius can't be written before the genius (me) arrives at the thread.

     

    If you haven't invented time travel so that you can post something before you posted it, you're not much of a genius.



  • @Someone You Know said:

    If you haven't invented time travel so that you can post something before you posted it, you're not much of a genius.

    You can be a genius and not a mad scientist.



  • @blakeyrat said:

    You can be a genius and not a mad scientist.

    Pics or it didn't happen.



  • @C-Octothorpe said:

    @blakeyrat said:

    You can be a genius and not a mad scientist.

    Pics or it didn't happen.



  • @Sutherlands said:

    @C-Octothorpe said:

    @blakeyrat said:

    You can be a genius and not a mad scientist.

    Pics or it didn't happen.

    Asshole...  I'm going to be tasting the coffee that shot through my nostrils for hours!



  • I'm still not sure of what that is supposed to be. It looks like the lovechild of a ferret and an armadillo.

     



  • @Zecc said:

    I'm still not sure of what that is supposed to be. It looks like the lovechild of a ferret and an armadillo.

     

    That's pretty close.



  • @C-Octothorpe said:

    @Sutherlands said:

    @C-Octothorpe said:

    @blakeyrat said:

    You can be a genius and not a mad scientist.

    Pics or it didn't happen.

    Asshole...  I'm going to be tasting the coffee that shot through my nostrils for hours!

    Glad I could help.


  • @Zecc said:

    I'm still not sure of what that is supposed to be. It looks like the lovechild of a ferret and an armadillo.

     

     

    It looks like a pangolin, which is a sort of anteater wearing armadillo armor.


  • ♿ (Parody)

    @Someone You Know said:

    @Zecc said:

    I'm still not sure of what that is supposed to be. It looks like the lovechild of a ferret and an armadillo.

     

     

    It looks like a pangolin, which is a sort of anteater wearing armadillo armor.


    Indeed.



  • Somehow I feel it is important to post a cartoon of a pangolin playing a mandolin:




  • Pangolins are kind of like Duck-billed Platupuses in that they are really weird mammals-with-stuff-mammals-don't-usually-have animals. The difference is that pangolins have a lousy press agent. Hell, you have to go 6 full pages down in Google Image Search before finding bad furry art of one! (Most animals have the bad furry art on page one.)

    All I know is that if I could do this:

    I'd basically never stop doing it.

    Oh, and pangolins are endangered, being a popular ingredient in Chinese bullshit "traditional" medicine! Save the pangolins.


Log in to reply