Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2012
    Posts
    5

    Unanswered: Enforcing Referential Integrity Error

    Hey guys,

    I am having a problem regarding a database I am trying to set-up. I've attached the file so you can see for yourselves.

    The problem is, that when trying to link tblCreditTransaction and tblCashTransaction to tblOrder while enforcing referential integrity, I get an error which prevents me from doing so as tblOrder includes every single transaction while the other two tables do not. However, it is not possible (as far as I'm aware), to include cash and credit sales in the same table as this would not fit the rules of normalisation (due to having empty cells in some of the records for cash sales).

    Does anyone know a way around this? I am aware that this may be asking too much, but any hints or tips, or pointing me in the right direction would be very much welcome

    Just for reference, tblSalesTransaction is the original table we were given to make a relational database from. All the other tables are ones I have created and migrated data across in an attempt to create the relational database.
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Why not include them on the same table, but simply put 0.00 for any fields that would otherwise be NULL (empty)?

    You can do this by setting up the default value for the field to 0.00, then unless you specifically change it, then each record will be generated with 0.00 for both the credit and the cash.

    As that's what's happening in real life, and would be sticking within the rules of normalisation... Seems like a simple enough solution to this problem.
    Looking for the perfect beer...

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Scrap that, I see what you mean now...

    But the way you've split your tables up isn't really correct.

    Normalisation is there to make things work more simply and effectively, not make it more complex.

    The occurrence of null values in some cases would indicate that those are the fields that need to be split to another table... So the customer details, sales area, etc, are what I'd move to another table, not financial details.
    Looking for the perfect beer...

  4. #4
    Join Date
    Feb 2012
    Posts
    5
    As you can see, I'm not great at normalisation.

    If I understand correctly, you're saying that I should keep both credit and cash sales in the same table, but have another table with customer reference and sales rep id (or something along those lines)?

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    I'm not sure why tblCashTransaction even exists...

    All it's doing is repeating information held already in tblSalesTransaction. That's the opposite of normalising your database.

    Normalisation is used to prevent repetition. Not create it.

    I think the design needs to be re-thought.


    The way I personally go about normalising, when I can't see a clear way, is put all the fields I need into one table. Populate it with some sample data. Say 100 records. Then look and see where things are being repeated.

    So, for example:

    If I see that [SalesRepresentativeID] 'A01' is always related to the [SalesRepresentativeForename] 'Craig' and [SalesRepresentativeSurname] 'Sutherland'.

    Then that would tell me that this needs to be normalised, because [SalesRepresentativeForename] and [SalesRepresentativeSurname], can be derived from the [SalesRepresentativeID] field.


    Likewise:

    If I see that [CustomerName] 'In Essence' is always related to the [CustomerAddress1] '29 Roseberry Avenue'.

    Then that would tell me that this also needs to be normalised, because [CustomerAddress1], can be derived from the [CustomerName] field.



    So, looking back at the first example...

    I would then create a new table, with the fields [SalesRepresentativeID], [SalesRepresentativeForename] and [SalesRepresentativeSurname] in it, exclusively.

    I would then delete the fields [SalesRepresentativeForename] and [SalesRepresentativeSurname] from the one main table I made in the beginning, but leaving the [SalesRepresentativeID] in it as a reference point.

    Then, whenever I wanted to know who was responsible for a given sale, all I have to store is the [SalesRepresentativeID] against the order, and their name could then be derived as and when I needed it.


    This keeps the data tidy, and, if for example, 'Craig' 'Sutherland' changed his surname to 'Fletcher', only one record in the new table would require updating, and every reference to him would automatically be updated correctly.

    Leaving it on the main table, would mean changing every single entry for 'Craig' 'Sutherland' (of which there could be potentially 100's of thousands), leaving a lot more room for error, as well as taking considerably more storage space.



    I hope my example(s) make it clear on how you should proceed with this database.

    Any other questions, give us a shout.
    Looking for the perfect beer...

  6. #6
    Join Date
    Feb 2012
    Posts
    5
    Just for clarification Kez, the tblSalesTransaction was the table we were given to start with, with all the data which we need to then normalise and create a relational database from, therefore once this has been done that table will be deleted.

    I agree it does need a re-design, and I also agree with your methodology for normalisation, as it is what I have used. However this is a more complex example than I have ever done before and there are small niggles (like the cash and credit sales) which are causing me problems. If there were only credit sales then I would have no problem with this project.

    I shall take your advice on making a main table with only so many records and trying from there.

    Of course if you come up with anything else that may help, that would be very welcome, thank you

  7. #7
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Nope...

    You want to strip out any derived bits of information from that table, moving them into new tables for each piece/group of derived information.

    Then once all the derived information is stripped, you'll be left with the bare basics in that table...

    So the invoice number, date, product purchased, salesRepID, customer, cash/credit, amount, etc. will remain in the main table.

    Then you'd have lookup tables for salesRepID -> First name & Last name.... Customer -> Address 1, address 2, city, country... etc.

    If you start with one table, you strip it down until it can't be stripped anymore. Then you'll end up with your original table, minus several fields, and a few extra tables to accommodate those removed fields.

    You'll want to have a main table, where the bulk of your info is stored. Just not the derived information. That is, the information that can be worked out, and therefore isn't necessary to be stored in that table, over and over again.
    Looking for the perfect beer...

  8. #8
    Join Date
    Oct 2011
    Posts
    2

    Another possibility

    If you really don't want to know the customer reference number & sales rep information for cash purchases, I'll think about it again.

    By the way, I didn't really take a good look at that SalesTransaction table.

    This looks suspiciously like an old homework assignment or something. In my image, credit is a yes/no field.

    I didn't see the need for separate cash and credit tables. There was no new information in either.
    Attached Thumbnails Attached Thumbnails 3BS.png  
    Attached Files Attached Files

  9. #9
    Join Date
    Feb 2012
    Posts
    5
    Thanks for the help guys.

    Just seen your response now Ymeoru, and I have setup something extremely similar to yours which is working fine at the moment.

    Yes, this is a university assignment, one which I believe has been used several years ago, just needed some help with the normalisation as I can do most of the query/form based stuff.

Posting Permissions

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