Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Newbie database design questions

    I am very new to the database design world and was wondering if some of you could look this over to see if everything is up to snuff.

    I am designing a backend database to track our internal and external IT audits. Here are the tables that I have created thus far but Im not quite sure how the relationships should work. I have included what I think would be PK and FK. I will also probably be adding to this as I think of more items to track.

    I am thinking of using MYSQL for the database but Im not 100%. Any help would be appreciated.

    Also I will probably need to design a web frontend for usability any suggestions on a preferred language to write it in.

    Thanks,

    Lee

    TableAssets:
    AssetTag PK
    AssetName
    AssetIP
    AssetDescription
    AssetOwnerID
    AssetValueID
    AssetLocationID

    TableAssetOwner:
    AssetOwnerID PK, FK
    AssetOwnerFirst
    AssetOwnerLast
    AssetOwnerPhone
    AssetOwnerEmail

    TableAssetLocation:
    AssetLocationID PK, FK
    BuildingName
    BuildingAddress1
    BuildingAddress2
    BuildingCity
    BuildingState
    BuildingZip.

    TableAssetValue:
    AssetValueID PK, FK
    AssetValue (Int)


    TableAudit:
    AuditID PK
    AuditName
    AuditDate
    AuditDescriptionID
    AuditVendorID

    TableAuditDescription:
    AuditDescriptionID PK, FK
    AuditDescription


    TableAuditVendor:
    AuditVendorID PK, FK
    AuditVendorName
    AuditVendorAddress1
    AuditVendorAddress2
    AuditVendorCity
    AuditVendorState
    AuditVendorZip

    TableAuditFinding:
    AuditFindingID PK, FK
    AuditFinding
    AuditFindingLikelihoodID
    AuditFindingRecommendationID
    AuditFindingImpactID

    TableAuditFindingRecommendation:
    AuditFindingRecommendationID PK, FK
    AuditFindingRecommendation

    TableAuditFindingLikelihood
    AuditFindingLikelihoodID PK, FK
    AuditFindingLikelihood (Int)

    TableAuditFindingImpact:
    AuditFindingImpactID PK, FK
    AuditFindingImpact (Int)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    - use plural table names
    - do not unnecessarily repeat part of the table name in the column name
    - use ID as the column name for surrogate keys (auto_increments)
    - do not use an integer surrogate to substitute for an integer value
    - foreign keys reference primary keys (se in red below)

    Assets:
    Tag PK
    Name
    IP
    Description
    OwnerID FK
    Value
    LocationID FK

    AssetOwners:
    ID PK
    Firstname
    Lastname
    Phone
    Email

    AssetLocations:
    ID PK
    BuildingName
    Address1
    Address2
    City
    State
    Zip

    Audits:
    ID PK
    Name
    AuditDate
    DescriptionID FK
    VendorID FK

    AuditDescriptions:
    ID PK
    Description


    AuditVendors:
    ID PK
    Name
    Address1
    Address2
    City
    State
    Zip

    AuditFindings:
    ID PK
    Finding
    Likelihood
    RecommendationID FK
    Impact

    AuditFindingRecommendations:
    ID PK
    Recommendation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    2
    Thanks r937 I just picked up Database Design for Mere Mortals and hopefully that will go a long way in the education process. I'm sure I'll have more questions later but for now I'll read the text.

Posting Permissions

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