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:
ADDRESS GENERALCONTRACTOR (foreign key, OneToMany) VENDORS (ManyToMany)
NAME PROJECT (Foreign Key, OneToMany)
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.:
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!