Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2005
    Posts
    10

    Too many tables after normalisation ?

    Hi Guys
    I am trying to create a database application. I have normalised the form used in the application. I then had 23 tables which i then Rationalised, bringing the total number of tables to 15. This seems alot of tables. I have been told i have to optimise them. How would you do that?
    Below are the Rationalised Tables:

    ------------------------------
    Customer

    Customer Number
    Customer Name
    Customer Address
    Town
    Postal Code
    Telephone

    ----------------------------
    Sale

    CustInvoice No
    CustInvoice Date
    Customer Number *
    Net Total
    Vat
    Grand Total
    Discount Code*

    -----------------------------
    Staff

    Staff No
    First Name
    Last Name
    Street
    Postal Code *
    Telephone
    Date of Birth
    Joined
    Next Of Kin
    Part Time/Full Time
    Pay Code *
    Special Note

    -----------------------------
    Purchase Order Invoice

    PurchaseInvoice No
    PurchaseInvoice Date
    Order No *
    Vat No
    SubTotal
    Discount
    Vat
    Total

    -----------------
    Discount (2)

    Discount Code
    Discount

    --------------------------
    Supplier (12)

    Supplier ID
    Supplier Name
    Address
    Postal Code
    Telephone
    Email
    Fax No

    ---------------------------
    Pay Code (23)

    Pay Code
    Rate of Pay

    -------------------------------
    Supplier Invoice

    PurchaseInvoice No *
    Supplier ID *

    --------------------------------
    Stock Order

    PurchaseInvoice No *
    Stock No *
    Quantity
    Cost

    ---------------------------------
    StockSale

    CustInvoice No*
    Stock Number *
    Quantity
    Cost

    ------------------------------------
    Stock

    Stock No
    Description
    Unit
    Price
    In Stock
    On Order
    Category
    Reorder Level

    -----------------------------------
    Purchase Order


    PurchaseOrder No
    PurchaseOrder Date
    Supplier ID*

    ----------------------------------------
    StockOrder


    PurchaseOrder No *
    Stock No *
    Quantity

    -------------------------------------------
    CustomerOrderDetails

    CustOrder No
    CustOrder Date
    Customer Number*

    -----------------------------------------
    Order

    CustOrder No
    Stock No*
    Quantity

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    What is Rationalization?

  3. #3
    Join Date
    Dec 2005
    Posts
    10

    Rationalisation

    Hi
    This is the process by which having normalised all the tables you join together those tables with the same Primary Key into one table. Those tables might have slightly different attributes.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    You think 15 tables is too many? You'd hate the database I work on then - it currently has 1,412!

    If your data is properly normalised then you have exactly the right number of tables.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    the number of tables you have is arbitrary. A good design could have 2 or 2,341,123 tables, it depends on the business need.

    The only red flag I see is what appears to be quite a few aggregate fields. Do you have a good reason for storing derived data?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    To make your final design more useable, you might want to hide multiple tables behind VIEWs. I've done that for some tables on our system just so the end-users don't have to write complex statements with a bunch of JOINs.

    i.e. if you have a ADDRESS table that has a country code that needs a look-up to a COUNTRY table, hide that join in a view:

    CREATE VIEW ADDRESS_DETAILS AS
    SELECT col1, col2, ... coln, COUNTRY_NAME
    FROM ADDRESS A, COUNTRY C
    WHERE A.COUNTRY_ID = C.COUNTRY_ID;

    Then the user can just say:
    SELECT * FROM ADDRESS_DETAILS;

    Much easier for them. If you like people who need it to be "easy" accessing your data.
    --
    Jonathan Petruk
    DB2 Database Consultant

  7. #7
    Join Date
    Dec 2005
    Posts
    10

    too many tables?

    Thanks for your replies guys.

    Teddy: the aggregate tables were derived from the normalisation process. I dont quite know what you mean by derived data. Explain please.

    J Petruk: Cheers for the suggestion.


    The reason i wondered about the size is that it is a fairly simple application and following the rules i came up with that number of tables.

    Cheers!!

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Derived data is exactly that, data which is derived from pre-existing information.

    For example:

    tblHasDerivedData
    -----
    someKey
    value1
    value2
    SumOfValue1AndValue2 'BIG NO-NO

    anything that can be calculated or "derived" from information t hat already exists in your database generally should not be stored. There are a few times you may want to do it, but not very often.

    With that in mind, i'm looking at your Net Total, VAT and Grand total fields wondering if they are derived from pre-existing information.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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