Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013
    Posts
    2

    Unanswered: Ensuring Unique Records in one-many table

    Hello all,

    A database beginner here. Tried searching but couldn't find the right answer....

    I have two tables as shown below. In this case, I want it so that I want a unique Dips record for each site for each DipDate.

    http://i118.photobucket.com/albums/o...rmis/table.png

    That is, I shouldn't have a a record in Dips table with an identical DeliverySite_ID and a DipDate.

    How can I accomplish this in an elegant manner? Do I need to rethink how I set up my tables, or does it have to be done through some kind of validation?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    There's several approaches
    either make the date the primary key or part of the primary key (jkn this case the PK could be the siteID and DipDate
    or create a unique index on the siteID and dipdate columns

    looking at your table design I'd suggest
    assuming you have defined the DipDate to be of type datetime (as you should for temporal data) then there is no need to have the additional column diptime as a datetiem column stores a date and time in the same column.
    so you can get rid of diptime as a separate entity
    I'd get rid of the ID column, as the dipdate is unique, so its a good candidate for primary key

    naming of columns
    everyone has their own hangups in this area
    only give columns meaningfull names so field1 is a no no
    theree is no point in repeating the tablename in the column definition
    so no need for DipDate, although date is a reserved word in Access sop you can't use date by itself. granted there is no technical reason why you can't use the tablename... just that when you come to write queires its carrying superfluous information
    eg
    SELECT Dip, DipDate,DeliverySite,DipTIme,Field1 FROM Dips
    most query designers will add the table
    SELECT Dips.Dip, Dips.DipDate, Dips.DeliverySite, Dips.DipTIme, Dips.Field1 FROM Dips

    so drop the DeliverySite prefix off the ID column in DeliverySite
    decide if you want to use CamelCase or underscore to separate words in a column/tablename, use one or t'other but not both

    Not knowing the requirements but Im suspicious of a column called 'payload', if thats the fixed delivery package then fine, but it has the feel of a specific delivery rather than a site definition

    YOu may care to look for the Access reserved words and avoid suing them in table/column names
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2013
    Posts
    2
    Thanks for the valuable info - very much appreciated. I'll have a crack at the solutions you suggest.

Posting Permissions

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