Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Need advice for relational database setup for my new project.

    Alright I am going to describe this a simple as possible. Please give any advice that you can it would be greatly appreciated.

    I am making a small project management system. The admins will be able to add PROJECTS as the main content type. Everything will be linked to a PROJECT within the system.

    When an admin adds a project this is what it should look like as far as fields are concerned:
    Name: TextField
    City: TextField
    State: SelectList
    Zip: TextField
    General Contractor: SelectList (with an add new general contractor button next to it)
    Buildings: Multiple TextFields to add many building names (ex: BLDG 1, BLDG 2, Community BLDG.) Next to each TextField there will be an UploadFileField to upload a materialist.txt file per building.
    Invite Vendors to Bid: SelectMultipleList (with an add new vendor button next to it)

    A couple of side notes to ease confusion:
    A Project can have only 1 General Contractor and a General Contractor can be assigned to many Projects.
    A Project can have many invited Vendors and a Vendor can be invited to bid the material lists of many Projects.
    A Project can have many Buildings and a Building can be assigned to only 1 Project. (When buildings are added they will be added when creating a project, then somehow as a Building is added a Material list will get uploaded for that building).
    A Building can have multiple materials. A material can be assigned to only 1 Building.

    Here are my models for now:

    PROJECT
    NAME
    ADDRESS
    GENERALCONTRACTOR (foreign key, OneToMany)
    VENDORS (ManyToMany)

    GENERALCONTRACTOR
    NAME
    ADDRESS
    CONTACTS

    VENDOR
    NAME
    CONTACTS

    BUILDING
    NAME
    PROJECT (Foreign Key, OneToMany)

    MATERIAL
    NAME
    QUANTITY
    UNIT
    BUILDING (Foreign Key, OneToMany - A material can be designated to only one of the project's buildings)
    (NOTE: An estimating program exports a material list txt file with 3 columns NAME, QUANTITY, UNIT. The program is going to pull from these columns parsing the list and add each piece of material to the database. That is how this material table is supposed to get filled.) My only concern is that a lot of these items will be the same per building. Won't many duplicates get added to my MATERIAL TABLE. I guess this wont matter if there is a primary key ID field.

    They will be listed something like this in the MATERIAL TABLE: I guess this is fine because the database will know what project they are linked to because the building will know. If someone sees something wrong with this please let me know.:
    ID NAME QUANT UNIT BLDG
    1 CEDAR SHIMS 20 EA BLDG 1 (project BOARDWALK HOMES)
    2 GE 5010 SILICONE CAULK(TUBE) 25 EA BLDG 3 (project BOARDWALK HOMES)
    3 GRACE VYCOR 12X75 10225 LF BLDG 2 (project BOARDWALK HOMES)
    4 CEDAR SHIMS 3 EA BLDG 1 (project MONOPOLY HOMES)
    5 GE 5010 SILICONE CAULK(TUBE) 15 EA BLDG 2 (project MONOPOLY HOMES)
    6 GRACE VYCOR 12X75 9999 LF BLDG 3 (project MONOPOLY HOMES)

    Note: The framework that I am using to write this automatically creates the connector table for my Many2Many fields. ex: ProjectVendors. So for now I just put a note near them such as (ManyToMany).

    I know this is a complete mess, but if you can kind of pick up on what I am trying to do and possibly edit my models or leave any advice possible that would be amazing!
    Last edited by hunterT; 01-25-12 at 15:22.

Posting Permissions

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