• Ingo (unregistered)

    Why split the project up in multiple tables? If you allow only one table per project then the "Everything" database could be sufficient for every in-house project ever built.

    But first.. we must have our hands on that loop trickery thingie he used. That way we can generate around 500 columns with random names that can be used if needed.

  • Sutherlands (unregistered) in reply to tgape

    [quote user="tgape"][quote user="danixdefcon5"]Except I wouldn't think about using arrays in a relational database, as it is a 1st normal form! If you got "array" attributes, well, that's a 1:n relation, and you store that in a related table.

    Anyway, these "clever" WTF tables are usually the product of people who think that DB tables equate to SpreadSheets![/quote]

    If you store your array values in a related table, then you can no longer do a query which depends on the main table and several values in the related table. This is, btw, TRWTF with relational databases.

    For example, one has a table with the fields:

    [quote]ID, NAME, ARRAYKEY[/quote]

    ARRAYKEY is the primary index on a second table:

    [quote]ARRAYID, VALUE[/quote]

    Under this setup, if I want to do a query of those entries where NAME matches 'part*', ARRAY[0] is less than 3, and ARRAY[4] is greater than 5, I'm out of luck. That's because when the join happens, it creates a number of virtual records, each of which only has a single array value. Likewise, one can't check for entries where no array value is outside of a given range - one would, instead, simply get all the rows with array values within the range.

    Now, of course, there are many databases that don't have this issue. Hierarchical databases, for example, generally are just fine with multi-valued data. (Of course, hierarchical databases have other issues, including enforced data redundancy.) I hear object-oriented databases may also handle arrays ok, although I've never actually worked with one.[/quote]

    Let's ignore for a second the WTF that is indexing specific places in the array, and not giving each number a position, and then realize that you put the indexing key in the wrong table, we can design the database like thus:

    Table representing the object with an array: [quote]ID, Name[/quote] And then create our array table: [quote]ArrayKey, Value, Position[/quote]

    So ArrayKey is the object to which it belongs, Value is the value, and Position is the position in the array. ArrayKey and Position form the primary key.

    Then we can do what you want (simplified and ignore any syntax issues): Entries where name contains part, array[0] is less than 3, and array[4] is greater than 5: [quote]Select name from table1 inner join table2 as firstpart inner join table2 as secondpart where table1.name ilike part, table1.id = firstpart.arraykey, table1.id = secondpart.arraykey, firstpart.position = 0, secondpart.position = 4, firstpart.value < 3, secondpart.value > 5[/quote]

    Your second one: "entries where no array value is outside of a given range": [quote]Select name from table1 where not in (select arraykey from table2 where value > x)[/quoet]

  • Sutherlands (unregistered) in reply to Sutherlands
    tgape:
    danixdefcon5:
    Except I wouldn't think about using arrays in a relational database, as it is a 1st normal form! If you got "array" attributes, well, that's a 1:n relation, and you store that in a related table.

    Anyway, these "clever" WTF tables are usually the product of people who think that DB tables equate to SpreadSheets!

    If you store your array values in a related table, then you can no longer do a query which depends on the main table and several values in the related table. This is, btw, TRWTF with relational databases.

    For example, one has a table with the fields:

    ID, NAME, ARRAYKEY

    ARRAYKEY is the primary index on a second table:

    ARRAYID, VALUE

    Under this setup, if I want to do a query of those entries where NAME matches 'part*', ARRAY[0] is less than 3, and ARRAY[4] is greater than 5, I'm out of luck. That's because when the join happens, it creates a number of virtual records, each of which only has a single array value. Likewise, one can't check for entries where no array value is outside of a given range - one would, instead, simply get all the rows with array values within the range.

    Now, of course, there are many databases that don't have this issue. Hierarchical databases, for example, generally are just fine with multi-valued data. (Of course, hierarchical databases have other issues, including enforced data redundancy.) I hear object-oriented databases may also handle arrays ok, although I've never actually worked with one.

    Let's ignore for a second the WTF that is indexing specific places in the array, and not giving each number a position, and then realize that you put the indexing key in the wrong table, we can design the database like thus:

    Table representing the object with an array:

    ID, Name
    And then create our array table:
    ArrayKey, Value, Position

    So ArrayKey is the object to which it belongs, Value is the value, and Position is the position in the array. ArrayKey and Position form the primary key.

    Then we can do what you want (simplified and ignore any syntax issues): Entries where name contains part, array[0] is less than 3, and array[4] is greater than 5:

    Select name from table1 inner join table2 as firstpart inner join table2 as secondpart where table1.name ilike part, table1.id = firstpart.arraykey, table1.id = secondpart.arraykey, firstpart.position = 0, secondpart.position = 4, firstpart.value < 3, secondpart.value > 5

    Your second one: "entries where no array value is outside of a given range":

    Select name from table1 where not in (select arraykey from table2 where value > x)

    (Hmm, quotes got mess up, obv)

  • TheIslander (unregistered)

    This database design (or lack of it) looks pretty close to the design used by my previous employer. Needless to say, I started the job search after the first month of employment. Their attitude can be summarized with these words: "Things are what they are". Wise words from a few of the morons that have spent 20+ years of their professional life working for the same employer and with the same app.

  • Orcus (unregistered)

    Ah ! another implementation of the golden nail pattern !

    sadly though I am dreaming of Update syscolumns set name = replace(name,'tbl','') on the mess I maintain :(

    How I miss SPOT has anybody seen him recently ? Or is everybody just cloning Pots to cook up more spagetti ?

  • (cs) in reply to Steven G. Aldana, Ph.D.

    Do we have to mention the Ph.D. when referencing you?

  • (cs) in reply to oldie
    oldie:
    grg:
    That's nothing.

    At my PPOP there was a very important database where the design had relations expressed by having field names in the database.

    Let me repeat that: there were VARCHAR fields containing the names of the related field. So every lookup had to do a SELECT just to get the name of the desired column, then you had to build another SELECT referencing that column.
    Slow and bug-prone. And there were plans to someday change this, a few years down the line.

    I'd just like to be the FIST!!!1!!111! to point out that the word "Relational" in "Relational Database" does not have anything to do with relationships between tables.

    Yeah, it means human-databse relationships and has nothing to do with tables. It's just how you are related to your database. Who comes to 7NF has reached Nirvana and is in perfect resonance with tables and triggers, fields and indexes...

    Obviously, these guys are still at 0NF, hence representing stones.

  • AlanGriffiths (unregistered) in reply to A Nonny Mouse
    A Nonny Mouse:
    264 columns? is it just me, or shouldn't the developer around the 20, 50, 100, 150 etc marks be thinking "there has to be a better way"
    But when they stop typing to think the PHB comes by and says "stop slacking, get coding". Pretty soon the idea of thinking about what they're doing fades in favour of looking busy.
  • (cs) in reply to AlanGriffiths
    AlanGriffiths:
    But when they stop typing to think the PHB comes by and says "stop slacking, get coding". Pretty soon the idea of thinking about what they're doing fades in favour of looking busy.
    Stop slacking off on the web! Get coding!
  • Mitsaras (unregistered)

    Well, if I had to work on that code, I'd sell my experience to become a horror movie later.

  • Hans (unregistered) in reply to rgro
    rgro:
    ouch! You really don't need arrays in a database.

    Can you point out where in the theory of relational databases, arrays are explicitly forbidden? It is more an implementation issue - almost no RDBMS currently on the market supports them.

    And while you strictly speaking do not even need an RDBMS, they can be damn convenient... Same as arrays: sometimes it is extremely convenient to have them.

    The fact that you can in fact emulate them with more tables is immaterial. You can also emulate strings with more tables, but noone is saying that the varchar datatype should never be used.

  • TInkerghost (unregistered)

    Hmmm, database design errors. OK, customer payment table with each feature, tax, fee, etc as it's own field --- 185 fields before the rewrite. That went with the independent tax code written in 4 different places all with hard coded field names & no loops - payment by credit card didn't total out the same as payment by check, and neither totaled to what displayed on the screen.

    Zip code fields set as integer - seen this before, but we're based in MA where most of the zip codes starting with '0'. The code actually artificially tacked a 0 to the lead when displaying something it pulled out that was 4 digits long.

    MA state tax website for paying property tax. The table only allows lot numbers that are integers --- my property has an A on the lot number.

  • Markus (unregistered) in reply to An apprentice
    An apprentice:
    Aaargh. Why is it that always when a truly horrible atrocity shows up on this site, it involves SQL and relational design?

    Because of the real-world equivalent: A list on paper.

    The physical limitations of paper almost inevitably lead to a design that is an atrocity in terms of something as flexible as data structures and as "alive" as related tables.

  • bd (unregistered) in reply to bjolling
    bjolling:
    bd:
    TRWTF is that databases don't support array fields. We all know from previous articles that every time you see bunch of variables with names containing numbers in a sequence, you can refactor all that into a single array.

    As usual the TRWTF are the comments

    Nah, TRRWTF is that I'll have to use <sarcasm> tags from now on.

  • wb (unregistered) in reply to bd
    TRWTF is that databases don't support array fields.
    Your comment is a WTF, sorry to say.

    Pick up a copy of Celko's "SQL For Smarties" and see how sequences can be generated by using relational algebra.

    Alternatively, you can create a table for this purpose, with each tuple representing a single value in the series.

    In SQL set operations are performed against tuples, or rows - there is no need to cram multiple data elements into one field.

    On the other hand I must commend on the very straightforward schema design which mirrors the user interface almost perfectly.

    You are kidding, right?

  • wb (unregistered) in reply to tgape
    tgape:
    Under this setup, if I want to do a query of those entries where NAME matches 'part*', ARRAY[0] is less than 3, and ARRAY[4] is greater than 5, I'm out of luck.
    1st of all, how can you know how many values are in the ArrayKey list?

    2nd of all, why have you attached significance to a position in the ArrayKey list?

    Reconstruct your problem definition in set based terms, and try again.

    Never express a 1:0-many relationship by way of an array or csv field; instead create a child table with a foreign key.

    Chances are you are not stating the problem correctly, and a simple range type of sql query with an inner join would work nicely.

    IMO you need to learn to think in set based terms to get the most out of sql. If you think something "can't be done" in SQL, most times you aren't thinking about the problem properly.

    And check out "Sql for Smarties" by Joe Celko.

  • wb (unregistered) in reply to Hans
    Hans:
    Can you point out where in the theory of relational databases, arrays are explicitly forbidden? It is more an implementation issue - almost no RDBMS currently on the market supports them.
    1NF as defined by Codd et. al forbids the usage of "relation-valued attributes" (tables within tables).

    http://en.wikipedia.org/wiki/First_normal_form

    It is a basic rule of domain integrity that each attribute (field) must represent exactly one value.

    Hans:
    The fact that you can in fact emulate them with more tables is immaterial.
    WTF? So what you are saying is that since you have not studied the basics of database normalization, then it is okay to do what YOU feel is correct when implementing a database.

    Well my friend, this is how WTF's happen.

    Hans:
    You can also emulate strings with more tables, but noone is saying that the varchar datatype should never be used.
    Apples and oranges. A varchar is a datatype - it ensures integrity on the domain (datatype) level.

    A csv or array, is not a scalar (single valued) datatype - as such, it breaks 1NF form.

    I hope you're not designing databases - no offense, but it seems clear that you have not learned the fundamentals.

  • n0rd (unregistered) in reply to An apprentice

    Because most people can't be bothered to learn about rational SQL/3NF design

  • (cs) in reply to tgape
    tgape:
    Under this setup, if I want to do a query of those entries where NAME matches 'part*', ARRAY[0] is less than 3, and ARRAY[4] is greater than 5, I'm out of luck. That's because when the join happens, it creates a number of virtual records, each of which only has a single array value. Likewise, one can't check for entries where no array value is outside of a given range - one would, instead, simply get all the rows with array values within the range.

    SELECT * FROM FOO, (SELECT * FROM BAR WHERE ARRAYIDX=0) BAR0, (SELECT * FROM BAR WHERE ARRAYIDX=4) BAR4 WHERE FOO.ARRAYKEY=BAR0.ARRAYID AND FOO.ARRAYKEY=BAR5.ARRAYID AND FOO.NAME LIKE 'part%' AND BAR0.VALUE < 3 AND BAR4.VALUE > 5

    This format with subqueries and cartesian products filtered by WHERE clause is standard for people writing SQL for Oracle. YMMV with database engines without armies of query optimizers.

  • Orcus (unregistered)

    You can't seriously mean there are differences between the implementation of getting a field from a rdbms, getting an element from an array in memory or a comma delimited character sequence from a file ?

    At least nobody showed this person XML. Then the table would of ended up with one text column named xmldata where the document schema wasn't common to each row in the table.

    There is a reason that there is a profession in database administration. Application developers that build these mirrors of arrays,lists and dictionaries in rdbms should really consider why some companies are willing to pay good money for a DBA to develop a data model.

    Still looking for an example peril of data Vision as the corollary though.

  • Greg (unregistered) in reply to Top Cod3r
    Top Cod3r:
    Look, if you are new to database design you might not be familiar with the concept of denormalization, which means you make changes to the database design for performance reasons based on the types of queries your application executes. For a timesheet app, it really makes sense to do it like this. It might seem strange if you have very little experience, but trust me on this.
    Yeah, right.

    And when you get a lot of experience maintaining your app, you renormalise.

    Rule of thumb for OLTP: when you are tempted to denormalise, you probably need a stored procedure. (Things are different for data warehouse apps.)

  • Greg (unregistered)
    ... the Everything Database: a single...database...
    That is not a WTF, it's the database philosophy. The premise is that the different parts of any given organisation use overlapping subsets of the same data. Therefore, it makes sense for data to be entered once and then available for use wherever it might be needed, without making redundant copies.

    Of course, there are situations in which it is valid to make subsidiary copies, or to partition the database. If you think you have such a situation, read Joe Celko's oeuvre, then Ask Your DBA.

  • Tarwn (unregistered)
    Anyway, these "clever" WTF tables are usually the product of people who think that DB tables equate to SpreadSheets!
    Which is why several years ago I started using the term XNF (eXcel Normalized Form) to reference these types of designs :)
  • Machine (unregistered) in reply to Annie Nymous
    Annie Nymous:
    A Nonny Mouse:
    T:
    A Nonny Mouse:
    "there has to be a better way"
    He or she may have used a loop to create the columns.
    "there has to be a better way"
    He or she may have used a script to generate the loop.
    "there has to be a better way"
    How about using a loop to create that script?
  • (cs) in reply to Top Cod3r
    Top Cod3r:
    The nice thing is the database virtually designs itself, it won't let you make mistakes.

    TRWTF is "programming" in Access and calling oneself "Top Cod3r"

    And The Biggest WTF is leaving a reference for those who have never heard of Access.

  • smartone (unregistered)

    The biggest WTF is that all of you have yet to realize what happens if a 31 day month starts on Saturday or Sunday or a 30 day month starts on Sunday.

  • Smithb182 (unregistered) in reply to oldie

    I conceive this website has got some rattling superb info for everyone. Dealing with network executives is like being nibbled to death by ducks. by Eric Sevareid. kefceddccdagfkeg

Leave a comment on “tblTimesheet”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article