Page 1 of 2 12 LastLast
Results 1 to 15 of 26

Thread: Two way index

  1. #1
    Join Date
    Oct 2003
    Posts
    84

    Unanswered: Two way index

    Hi,
    Does anyone have a clever way to do this. I want to be able to have a Table:

    TableA where two fields must contain unique integer values.

    TABLEA (A INT, B INT);

    I can prevent duplicates from being inserted using a two field unique index
    on (A, B). This prevents me from inserting something like

    1,3 OK
    2,3 OK
    1,3 ERROR

    But how do prevent a value from Field B from being used in field A, or vice versa. I want to prevent the following

    1,3 OK
    3,4 ERROR

    Is there some way to use comuted columns (or something else in order to achieve this), or do I have to resort to INSERT/UPDATE triggers?

    If what I explained sounds confusing, all I want to is to make value inserted into a column (A OR B) to be unique in both (A AND B) columns for all the other rows. Thanks in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think you're breaking second normal form here...

    Why do you want to do this?

    I don't think (and that happens all the time) that a computed column will work.

    I'd use a trigger....oh wait, I wouldn't do this at all....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I was thinking CHECK Constraints...but no dice

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 int, Col2 int, CHECK(Col1 NOT IN (SELECT Col2 FROM myTable99)))
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Has to be a trigger
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't do it at all. Obviously there is an issue with the schema design (breaking rules of normalization). Anybody with any experience on this forum can tell you that if you continue down this path you are going to spend a great deal of time coding around this issue and attempting to debug problems that occur. Best advice is to redesign your table (split off a subtable, most likely...).
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm just racking my brain though for any practical application....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Oct 2003
    Posts
    84
    This is what I'm trying to do:

    I'm collecting presence data (EmployeeID, Date and time, other info...) in one table. During an undetermined period, the user accesses this data through a VIEW that generates EmployeeID-StartDateTime-EndDateTime pairings based on the date and time of the presence data (chronologically). For example:

    PresenceID EmployeeID DateTime
    [1] [001] [2005-01-20 7:30]
    [2] [001] [2005-01-20 11:30]
    [3] [001] [2005-01-20 12:30]
    [4] [001] [2005-01-20 17:00]

    will generate
    [001] [2005-01-20 7:30] [2005-01-20 11:30]
    [001] [2005-01-20 12:30] [2005-01-20 17:00]

    meaning the employee worked from 7:30 to 11:30 then 12:30 to 17:00.
    This way the user can make changes..add entries (in case the emp forgot to punch), etc...When all changes are ready, I want to transfer these StartDateTime-EndDateTime pairs to a separate table. The table will not use the actual dates, but the presenceID's from the original presence table as foreign keys. This ensures that entries cannot be deleted if they hae been transfered (unless the foreign key entry specifies to cascade a delete).

    That is why I wanted a unique index on both fields, in both directions. Each entry made when an employee clocks in (or out) should never be used more then once....hence the weird unique index I wanted.

    I went to all this trouble because I wanted to avoid using a table that has
    StartDateTime-EndDateTime entries as fields and all punches and modifications are done on that table....For example if the employee forgets to clock in or out once, all other punches are offset by one....all clock-ins become clock-outs and vice versa. With my setup I can simply INSERT (or DELETE) a presence and everything is adjusted when presented to the user....

    Any ideas?
    Thanks a lot

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Any Ideas?

    Sure

    Your Data model is wrong.

    You descibed a unit of work for an employee.

    That has a start AND an end...1 row...I would use a trigger to prevent the adding of a new row until the end time was completed...even if it was after the fact, like the next day.

    And the have a constraint to make sure the end datetime is greater than the start datetime

    I would even use a trigger to make sure that that start datetime was greater than MAX(EndDatetime).

    Otherwise uniqueness doesn't by you anything...can you imagine the nightmare with datetime overlaps?

    But that's just me

    MOO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Oct 2003
    Posts
    84
    I was afraid my model was wrong.....

    But I can't really tell if someone is punch in or out....all I know is WHO it is and WHEN he is punching...You see, if someone forgets to punch out at the end of his shift....and the next morning comes to work and punches in...that punch becomes yesterday's out....(if I use the Start - end pairing in one row)..this can go on for a while until the employee times are tabulated....I have to offset everything to fix it.....all OUTS become INS and INS become OUTS. I was sure if
    my model was bad...but I thought...units of work (start and end times) are only when you want to calculate total time eg: payroll. At the start they are just that: punches that can be deleted/inserted/modified in case of user error....it's just thay I present it in a way that the user understands (paired off)...as for overlapping dates...it didn't occur...I had some query that would pair off everything (according to the long, long tests I did) perfectly for any number of punches, employees, etc....thanks for the ideas, advice....I'll try and work something out....

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You model is bad because your physical environment is bad. Why can't you differentiate between a punch-in and a punch-out? Hardware deficiencies? This is going to cause you no end of problems, especially since it is going to erode people's confidence in the system that their paychecks are derived from.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Oct 2003
    Posts
    84
    Well I can only assume that someone who punches is punching out if
    there is a corresponding punch-in record (with a null punch-out). In that case I
    can issue an update statement to fill in the extra data. If there is not punch-in record with a null punch-out...I can issue an insert statement to set up a new record with the startdatetime set and a null enddatetime.

    The problems arise when users forget (and they do, and quite often) forget to punch. It throws everything off....all punch-ins following the forgotten punch become punch-outs and vice versa.....(if I use the logic stated above).

  11. #11
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    As blindman points out, why not change your 'punch' to a 'punch in' / 'punch out'? If someone tries to punch in prior to punching out, punch 'em with an error message...

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by MaxA
    As blindman points out, why not change your 'punch' to a 'punch in' / 'punch out'? If someone tries to punch in prior to punching out, punch 'em with an error message...

    As Blindman points out?

    TGIF!
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Oct 2003
    Posts
    84
    Well that's the problem...I can't tell if they're punching in or out...
    It's not like I can enforce a 9-5 schedule and tell from there whether the guy is punching in or out (if you take night shifts into consideration). All I know is Who punched (cardID read from reader) and WHEN (Current server date). I can only ASSUME something is off...for example:

    if someone comes in and punches at 7:30 AM, I get


    EmpID-StartDateTime-EndDateTime
    "Emp001"-"2005-01-21 7:30"-NULL

    he forgets to punch in at 5:00 PM when he leaves....the next morning punches in at 8:00 AM....I would then end up with

    EmpID-StartDateTime-EndDateTime
    "Emp001"-"2005-01-21 7:30"-"2005-01-22 8:00"

    I can ASSUME he didn't work 24 hours.....but thats all it is...an assumption. for the rest of the week period...let's say he works 8:00 AM 5:00 PM his punches will look like this:

    EmpID-StartDateTime-EndDateTime
    "Emp001"-"2005-01-22 17:00"-"2005-01-23 8:00"
    "Emp001"-"2005-01-23 17:00"-"2005-01-24 8:00"
    "Emp001"-"2005-01-24 17:00"-"2005-01-25 8:00"
    "Emp001"-"2005-01-25 17:00"-"2005-01-26 8:00"

    .... and so on...That is why I orinially thought of using a single row for a punch...

    EmpID-DateTimeOfPunch


    and an insert is performed everytime an employee punches (be it in or out...).
    When the user needs to view/modify this information...I have this query that makes In-Out pairs based on the single punches.....Therefore correcting an employee's forgotten punch is simply a matter of inserting a DateTimeValue for that employee and re-running the query that makes the pairings and everything works out...Same thing when deleting a punch that was not supposed to be made....delete the corresponding punch...re-run the query and presto. Now, when comes the time for payroll (and the user is sure all the
    punch data in front of him is correct...I can transfer all the individual punches into a seprate table that this time has StartTime-EndTime in each row (allowing me to calculate a length of time worked....this is the point in time that the unit of work (mentionned in a previous post by brett kaiser) became relevant)

    It just seemed to make it easier to work with this way....instead of using StartDateTime-EndDateTime right from the start...when many changes are applied to the punch data before being finalized for payroll...
    Thanks for all the advice so far....I'm trying to figure out the best way to do this...

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't hate me 'cause I'm beautiful...
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Oct 2003
    Posts
    84
    Quote Originally Posted by blindman
    Don't hate me 'cause I'm beautiful...

    ?!?!?!
    um, ok

Posting Permissions

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