Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Coder
    Join Date
    Jul 2006
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    are these tables normalised??

    ok here goes...... below is the table architecture of my database, I was wanting to know if someone could tell me if these tables are normalised and if not could they point me in the right direction.

    One of the tables which I wasn't sure if it was normalised was 'tblJob' because of the number of foreign keys. So, would it would be better to split the Job Table into two and have the JobID as the primary key in tblJob and create a table 'tblJobDetails' which would store all the foreign keys that are in the current table, 'tblJob'??? Would it be 'BETTER' to leave tblJob in its current state (the way it is listed below) , or should I change it to the way I have just mentioned, by splitting it into two separate tables???


    tblAuthorisation
    |AuthID(PrimaryKey)|AuthName|

    tblCar
    |Registration#(PrimaryKey)|CustomerName(ForeignKey)|Make|Model|Type|DateOfManufacture|MOTexpiry|TAXe xpiry|

    tblCarParts
    |CarPartID(PrimaryKey)|Part#(ForeignKey)|Registration#(ForeignKey)|

    tblCustomer
    |CustomerID(PrimaryKey)|Name|Address|Town/City|PostCode|Country|TelNo|

    tblHourlyRate
    |HourlyRateID(PrimaryKey)|HourlyRate|

    tblJob
    |JobID(PrimaryKey)|
    |MechID(F_Key)|
    |AuthID(F_Key)|
    |CustomerName(F_Key)|
    |Registration#(F_Key)|
    |Lab#(F_Key)|
    |Reason|
    |Part#(F_Key)|
    |Qty|
    |SubTotal|
    |VatID(F_Key)|
    |Total|
    |Paid|
    |DateOfManufacture(F_Key)|
    |JobFinished|
    |Date|

    tblLabour
    |Lab#(PrimaryKey)|Description|Duration|

    tblMechanic
    |MechID(PrimaryKey)|MechName|TelNo|

    tblOrders
    |Order#(PrimaryKey)|CustomerID(F_Key)|SubTotal|VatID(F_Key)|Total|Date|Received|

    tblOrderItems
    |OrderItemsID(PrimaryKey)|Order#(F_Key)|Part#(F_Key)|Qty|

    tblStock
    |Part#(primaryKey)|SupplierID(F_Key)|PartName|RetailUnitPrice|QtyInStock|ReOrderLevel|DateIntroduced |DateDiscontinued|

    tblSupplier
    |SupplierID(PrimaryKey)|SupplierName|Address|Town/City|PostCode|Country|TelNo|

    tblVat
    |VatID(PrimaryKey)|VatRate|



    Irish87.

  • #2
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Someone else may answer better but I think you are pulling in foreign keys to your table where, instead, your query could do it, possibly by using the join function.

    So instead of creating so many fields, which (I think), may cause problems later, when you update info, or delete records etc), the query can pull in the data it needs.

    bazz

  • #3
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,092
    Thanks
    2
    Thanked 23 Times in 23 Posts
    Quote Originally Posted by bazz
    Someone else may answer better but I think you are pulling in foreign keys to your table where, instead, your query could do it, possibly by using the join function.
    But you do the joins via the primary and foreign keys, so all those foreign keys are definitely needed. I think the database design is fine the way it is. You have your tables separated into logical groupings and foreign keys which link them all up, as appropriate.

    I did note a few points of picky-ness that I would personally change. You have CustomerName as a foreign key in tblJob, but apparently the primary key for that is CustomerID. Instead of naming it CustomerName, I would name it CustomerID so the relationship between the two is clearer.

    You have a table called tblLabour whose primary key is Lab#. I would change that to LabourID, again for clarity, and change the foreign key in tblJob so that it matches that also.

    If you're going to name your table tblStock, then I would expect the primary key to be called StockID, rather than Part# or PartID. You might also want to change the table name to tblParts, and then PartID for the primary key.

    In any event, make sure the foreign key names match the primary key. That's not a requirement, but a best practice kind of thing.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #4
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    But isn't a foreign key intended to bring in another table by using its primary key? I'm still a novice so I'm probably wrong.

    However, in table tblJob, the foriegn key for dateOfManufacture, points to a field rather than to tblCar.


    bazz

  • #5
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,092
    Thanks
    2
    Thanked 23 Times in 23 Posts
    Quote Originally Posted by bazz
    But isn't a foreign key intended to bring in another table by using its primary key? I'm still a novice so I'm probably wrong.
    You have primary key in one table and foreign key in another table. The related records between two tables are then connected via a join of the primary and foreign keys. You need foreign keys everywhere that related data in another table needs to be joined to it.

    What you said in your original post is correct. When creating, updating and deleting records from the database, your scripts must maintain data integrity between the tables. So for example, if a CustomerID is deleted from the database, you may want to delete the records with that CustomerID from the related records from other tables. I say "may" because you might want to have a field like EndDate to reflect that a contract has been terminated with the customer or something like that.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #6
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Quote Originally Posted by vinyl-junkie
    You have primary key in one table and foreign key in another table. The related records between two tables are then connected via a join of the primary and foreign keys. You need foreign keys everywhere that related data in another table needs to be joined to it.
    Ah, so does that mean that a foreign key can point to a field in another table and not necessarily to its primary key?

    bazz

  • #7
    Senior Coder
    Join Date
    Jul 2005
    Location
    New York, NY
    Posts
    1,084
    Thanks
    4
    Thanked 19 Times in 19 Posts
    One thing you want to be careful of with financial data, like the vat rate, is changing financial data on historical records.

    So for example, let's say VAT #5 has a rate .05 this year, and last year it was .04. When you go back to look at last year's data, do you see the vat rate as .04 or .05? The answer SHOULD be .04, but if you use the foreign key and join against your historical data, the VAT rate will change depending on what time you look at the record. In 3 years, that record's VAT rate will be different from now which will be different from when it was entered.

    So while it SEEMS like the VAT rate should be "normalized" through the use of a relation table, in fact, the VAT rate is a historical piece of data, and not something that is "relational". This problem is encountered all the time in financial data. The best way to tackle it is to use your VAT table as a lookup for users to choose a VAT rate, but when you insert the record in your job table, instead of inserting the key of the VAT rate, you insert the VAT rate itself, and then your financial data will remain constant over time.

  • #8
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,092
    Thanks
    2
    Thanked 23 Times in 23 Posts
    Quote Originally Posted by bazz
    Ah, so does that mean that a foreign key can point to a field in another table and not necessarily to its primary key?

    bazz
    I could be wrong, but I don't think it would be considered a foreign key if it did that.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #9
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    It doesn't necessarily have to point to the primary key, it could refer to a different 'candidate key' (a column or combination of columns that could be the primary key, but was chosen not to be) if you really wanted it to (and that key had the appropriate 'unique' constraint) but it wouldn't be particularly sensible.

  • #10
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    That's a very useful tip Beagle.

    I'm not hijacking the thread but the answer to this would (I think), help irish87 and me.

    OK then, so in his post, he showed these two tables

    Code:
    tblCar
    |Registration#(PrimaryKey)|CustomerName(ForeignKey)|Make|Model|Type|DateOfManufacture|MOTexpiry|TAXe xpiry|
    
    tblJob
    |JobID(PrimaryKey)|
    |MechID(F_Key)|
    |AuthID(F_Key)|
    |CustomerName(F_Key)|
    |Registration#(F_Key)|
    |Lab#(F_Key)|
    |Reason|
    |Part#(F_Key)|
    |Qty|
    |SubTotal|
    |VatID(F_Key)|
    |Total|
    |Paid|
    |DateOfManufacture(F_Key)|
    |JobFinished|
    |Date|
    In tblJob he wants to get the value of DateOfManufacture from tblCar.

    Is the way he has shown it correct (when it seems that DateOfManufacture isn't the primary key) or, if not, what is the correct way.

    bazz

  • #11
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    it would help to create an ER diagram of the db...
    with a tool like Aqua Data Studio ( http://www.aquafold.com/index.html ) that would be very easy to generate.

    Quote Originally Posted by Beagle
    So while it SEEMS like the VAT rate should be "normalized" through the use of a relation table, in fact, the VAT rate is a historical piece of data, and not something that is "relational". This problem is encountered all the time in financial data. The best way to tackle it is to use your VAT table as a lookup for users to choose a VAT rate, but when you insert the record in your job table, instead of inserting the key of the VAT rate, you insert the VAT rate itself, and then your financial data will remain constant over time.
    i think you're mixing a few concept here.
    db-normalisation, historical positioning and relational design are three completely seperated issues.
    you are also mixing db-design requirements and reporting requirements which is limiting the useability of your data.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #12
    Senior Coder
    Join Date
    Jul 2005
    Location
    New York, NY
    Posts
    1,084
    Thanks
    4
    Thanked 19 Times in 19 Posts
    Yes, they are indeed separate issues. Specifically though, they are issues that seemed as though they weren't being considered by the OP. I know he asked for normalisation, I was pointing out that if he didn't separate out his historical data from his relational, than he would be running into problems, especially in a production environment involving financial transactions. I thought it was important enough to point out.

    Perhaps I'm missing a larger point, and would appreciate a further explanation of the three concepts you mentioned, as I also believe it would help bazz and the OP, as well as myself and other readers, to hear a more full explanation of the concepts.

  • #13
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Beagle
    I know he asked for normalisation, I was pointing out that if he didn't separate out his historical data from his relational, than he would be running into problems, especially in a production environment involving financial transactions. I thought it was important enough to point out.
    you are continuing to mix up different concepts.
    for instance, you invent notions like 'historical data' as compared to 'relational data' (i suppose you meant that).
    there simply is no such thing as 'historical data' --> run a google for it and you wount even find any db-design (or database) related links for that. (even 'historical data database' wount pull up relevant links)
    i suppose you just mean 'data that can change over time'

    even talking about 'relational data' is actually not saying much. You can structure all sorts of data in a relational structure (even your 'historical data') as well as in an hiërarchical or generic datamodel.
    Quote Originally Posted by Beagle
    Perhaps I'm missing a larger point, and would appreciate a further explanation of the three concepts you mentioned, as I also believe it would help bazz and the OP, as well as myself and other readers, to hear a more full explanation of the concepts.
    ooh. i almost feel challenged now! (let me build up some confidence again by flashing my badge
    although, since i recently finished a 3 years project (creating a DWH for life insurances from scratch) that involved some "financial transactions" "in a production environment" and since i do have about 8 years addition experience in all sorts of db related work (like data-analysis, datamining, data base drive websites, BI-systems, ...), i should be up to that task.

    of course i'm not gonna give "a more full explanation of the concepts" --> do your homework and run a few googles. all these concepts are very well documented in thousands of books/articles/tutorials/...

    i was just pointing out that you were mixing all sorts of concepts (+ you also added some of your own inventions).
    you "thought it was important enough to point out" and think that "Perhaps I'm missing a larger point" so let's have a closer look at what you were pointing out.
    Quote Originally Posted by Beagle
    One thing you want to be careful of with financial data, like the vat rate, is changing financial data on historical records.
    what are historical records? but ok, there comes an example
    Quote Originally Posted by Beagle
    So for example, let's say VAT #5 has a rate .05 this year, and last year it was .04.
    ok. so we have a dimension that changes over time. not realy uncommon.
    Quote Originally Posted by Beagle
    When you go back to look at last year's data, do you see the vat rate as .04 or .05?
    Good question!
    Quote Originally Posted by Beagle
    The answer SHOULD be .04
    hmm... here you're missing the ball a bit.
    you see, your db-design should allow a maximum of flexability. You should be able to set up your db so that it depends on the functional requirement what the VAT should be. In some cases, this will be .04, but there might as well be cases where it is .05. For instance, say the VAT is 0.04 in 2005 en 0.05 in 2006. What if someone orders something on 30/12/2005, but purchase is actually made on 02/01/2006?
    Or the reverse: good client comes in last minute and gets some supplies, but his order is only entered on 02/01/2006.
    you see? depending on your functional requirements, you should be able to get the rate at the date when the order was placed, at the date that the invoice was created, at the current date (if you want to make revenu comparisons) etc
    that's the real idea behind historical positioning in a dimension-table: that you can get the value at the point in time that best suits your functional requirements for a specific task. different taks might mean a different VAT...
    but moving on
    Quote Originally Posted by Beagle
    , but if you use the foreign key and join against your historical data, the VAT rate will change depending on what time you look at the record.
    wow! seriously missing the ball here!
    you seem to assume that tblVat can only contain one record.

    take the above situation (2005 = 0.04 and 2006 = 0.05)
    if tblVat contains these two records

    1 | 0.04
    2 | 0.05

    and the records in tblOrders that were created in 2005 have VatID=1
    and the records in tblOrders that were created in 2006 have VatID=2

    what would then be the problem?
    just plain and simple relational design with PK-FK relationship. no problems there.

    now of course, in this situation, that is probably not the best design --> i say this because i can not think of any functional requirement, or any use of the order data where this design is the best sollution. but lets move on
    Quote Originally Posted by Beagle
    In 3 years, that record's VAT rate will be different from now which will be different from when it was entered.
    same as above --> completely not true
    Quote Originally Posted by Beagle
    So while it SEEMS like the VAT rate should be "normalized" through the use of a relation table,
    huh? a relation table? there is no relation table here. it's a simple 1-many PK-FK relation so there wouldn't be any need for a relation table (which are only used for many - many relationships in a normalised db).
    and whats this 'VAT rate should be "normalized" through the use of a relation' you can not normalize a single attribute and normalization through relation tables is certainly not what this SEEMS like.
    Quote Originally Posted by Beagle
    in fact, the VAT rate is a historical piece of data, and not something that is "relational".
    there you go again with this 'historical piece' and 'relational'. Like i earlier said; it's not because the value in a dimensiontable can change over time, that this would create problems to use it in a relational datamodel or any other datamodel
    Quote Originally Posted by Beagle
    This problem is encountered all the time in financial data.
    what's the fixation with financial data here? you have dimensiontable that change over time in all sectors.
    anyway
    Quote Originally Posted by Beagle
    The best way to tackle it is to use your VAT table as a lookup for users to choose a VAT rate, but when you insert the record in your job table, instead of inserting the key of the VAT rate, you insert the VAT rate itself, and then your financial data will remain constant over time.
    ho man! 'the best way'? i haven't read any reason why this would be better then the setup he now has + i know of 'better ways'. that is
    - 'ways that allow more flexability, like multiple historical positioning possebilitys';
    - 'ways that will result in a more performant application';
    - 'ways that will resull in a better normalised db';
    - 'ways that would result in a design that could be used with multiple different VAT systems'

    (besides, you're using the term 'lookup' wrong (or at least confusing) here, since a lookup on a table is something completely different as populating a dropdown from a table)

    maybe we should look at these ways to give some other possible sollutions that might challenge your 'best way'.

    A design that allows multiple historical positioning:
    we change
    tblVat
    |VatID(PrimaryKey)|VatRate|

    into
    tblVat
    VatRate|from_date|to_date

    and remove the VatID from tblOrders.

    we can now get the rate by historical positioning in tblVat at the date of our choice (date of tenter, of order, of incoice ...)

    A design that will result in a more performant application:
    even the current design will result in that. I think that your sollutions is actually pretty bad in terms us userfriendlyness and performance. VAT-rules are quite strict and can easely (and should be) implemented in your db-model an/or app instead of relying on the user to select the appropriate rate.

    A design that will result in a better normalised db':
    now this one is harder to explain because i don't realy know the involved VAT system.
    but i can't realy imagine that the VAT-rate is the same for all clients and all parts.
    in most countrys, you'll have the principle that the VAT increases from elementary goods to luxury goods.
    thingls like 'bread' should then have a VAT of 0.02 and 'sigarets' will have a VAT of 0.10.
    In belgium for instance, you'll see the VAT for each seperate item on an invoice.
    so in this db-design, it would then be a normalisation requirement to place the VatID in the tblOrderItems table instead of in the tblOrders table

    A design that would result in a design that could be used with multiple different VAT systems:
    this is not just a matter of internationalisation. it's also a matter of flexability and setting up a design that can cope with all foreseeable future changes. i frequently get annoyed with misplaced 'yeah, but i didn't know that this would become a requirement when i set up the db' excuses from wannabe db-designers that read 2 articles on db-design and think there isn't more to it then a few normalisation and indexing principles.
    when you set up your db, you often have multiple designoptions that are all more or less just as performant and maintainable, but that can have huge differences on the ability to cope with future requirements.
    i (and others) have posted about these tradeoffs and the importance of flexability in many posts here and on other forums (for instance http://codingforums.com/showthread.p...ce+flexability and http://codingforums.com/showthread.p...mance+tradeoff )
    a good way of achieving that here, is looking at the VAT systems in other country.
    For instance, in belgium, we have these VAT-rates based on a productclassification.
    So it could be sensible to add a productgroup table, and to add the PK of that table as FK in the tblCarParts table
    And there are also a lott of countrys where some buyers don't need to pay VAT. For instance governemental organisation. Or private clients that don't need to pay VAT for second hand goods, whereas companys do need to pay VAT under certain conditions.
    So it might be sensible to add yet another clientsegment table and then create a facts-table like

    VatCategory
    VatCatID | productgroupID | clientsegmentID |VatID

    where you then use the VatCatID as FK inside the tblOrderItems. and tblVat could then look like
    tblVat
    VatID | VatRate | from_date | to_date
    where VatID is not the PK but just an indexed, not unique index.

    You see? The design gets a bit more elaborated but it will allow you to handle a lot more VAT situations that sooner or later could occur, without requiring you to go implement all sorts of business logic in your application.

    anyway, long post to point out that your post is:
    - not correct
    - not complete
    - at least very confusing because you use all sorts of concepts and terms in the wrong context and because you are combining terms in such a way that it doesn't make sense
    - not good advice imo
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #14
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Wow Raf, that was very informative on a number of key areas; not least that the VAT issue is now much clearer (and simpler), for me to understand.

    my little question a few posts ago has got lost in the flow of answers

    Where in one table, I want to call in a field value from another, should it be called by using the primary key of that second table, (and rely then on the query sifting through its fields) or, is it better to call the specific field directly?

    Bazz

  • #15
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by bazz
    Where in one table, I want to call in a field value from another, should it be called by using the primary key of that second table, (and rely then on the query sifting through its fields) or, is it better to call the specific field directly?
    i don't understand the question.

    i suppose you are asking about the
    tblJob
    |JobID(PrimaryKey)|
    |MechID(F_Key)|
    |AuthID(F_Key)|
    |CustomerName(F_Key)|
    |Registration#(F_Key)|
    |Lab#(F_Key)|
    |Reason|
    |Part#(F_Key)|
    |Qty|
    |SubTotal|
    |VatID(F_Key)|
    |Total|
    |Paid|
    |DateOfManufacture(F_Key)|
    |JobFinished|
    |Date|

    ?

    Well it depends on to which table the "DateOfManufacture(F_Key)" is pointing.
    it's not uncommon to have a table with 1 row for each day (like if you have additional variables for each date, besides stuff you can get with mysql functions (like the date and weekday)) but i don't think that is the case here...
    maybe he just made a copy-paste error or so...

    anyway, i don't understand what you mean by
    Quote Originally Posted by bazz
    I want to call in a field value from another
    and
    Quote Originally Posted by bazz
    and rely then on the query sifting through its fields) or, is it better to call the specific field directly?
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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