If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Need advice for relational database setup for my new project.

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 01-25-12, 11:52
hunterT hunterT is offline
Registered User
 
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 14:22.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On