Access Database to help manage 3D Model production

[taholmes160]

Hi Folks:

Im trying to develop a database to handle a lot of the details of 3D model and visualization production, Let me lay a few things out for you:

1. The major unit of work is the PROJECT
2. Each project may contain 1 to many SHOTS
3. shots contain ASSETS

For example, the project called Small Apartment contains 5 shots - exterior, living room, bedroom, bathroom and kitchen
Shot loving room has the following assets


1. Floor
2. Walls
3. Ceiling
4. Recliner
5. Book case
6 Rug
7. TV
8 TV Stand
9. Chair
10 Ottoman
11. End Table

Each Shot also has the following steps to it
1. Layout,
2. Camera,
3. Lighting,
4. FX,
5. Rendering.

Each step can have one of the following states (ideally this list should be changeable on a project basis)
1. To Do
2. In Progress
3. On hold
4. Blocked
4. Finished

Each asset in a shot likewise has the following steps - Ideally this list should be editable
1. Concept
2. Modeling
3. Textures
4. Rigging

each of these steps can also have one of the states listed above, as well

Each asset should be linked to its shot and project also have the following fields
- Asset ID
- Name
- Assigned to
- Due date (if any)
- Some way to add notes on a recurring basis

Each shot should have be linked to its project and also have the following fields
- Shot ID
- Name
- Assigned to
- Due date if any
- Some way to add notes along the way

The problem is, Im really not sure how to attack this project, particularly since I want to make sure the data is properly normalized for good operation of the DB

I would really love to hear some suggestions of how to set this up

Thanks A bunch
TIM

[CJ_London]

A pretty clear explanation of what is required. However it would help to understand the context of this requirement - what is the db for? how will it be used? what sort of outputs are required?

Each relationship you have defined is basically set out as one to one (ignoring many shots) - whereas there are potentially some one to many. for example TV - might also be one in the bedroom or another project. So the question is is it just a 'TV' or is it a specific asset, either generically defined, perhaps by it's size or model number or specifically by its serial number?

[taholmes160]

However it would help to understand the context of this requirement - what is the db for? how will it be used? what sort of outputs are required?

Hi Ajax and others:

This database will be used to help me track the various steps of the production of visual rendering of rooms or structures in 3d Rendering software (blender3d)

I'll use it as a way to keep track of all the bits and bobs that have to go into the successful production of a architectural rendering. As you can see from the list theres a lot of fiddly bits that have to go on, and lots of things that need dealt with, so not only are the checkoffs important, but so is the ability to make notes for each piece and keep track of them.

In terms of outputs, it will be on screen stuff -- I dont anticipate any need for paper output -- i'll mostly be interacting with the forms updating things as they get done etc.

Here is an example of a similar database, but geared for movie production, rather than for stills (there are significant differences)

This is from the shot screen of the database



You can see here the list of shots with the different steps for each one. Clicking on a particular step brings up the right side panel where notes for that particular task can be entered etc

It would be similar for the assets, and in their view, they also have a task list which is a compilation of all the tasks assigned to you

Thanks
TIM

[CJ_London]

thanks, that helps.

Per my second paragraph - you need to clarify assets - at the moment, you would potentially has an asset called TV in your assets table multiple times.

moving on, steps sounds like these apply to all shots so these would be field names in your shots table - and populated with one of the states values

Each asset should be linked to its shot and project

only to shot - you can get to project from the shot record

[taholmes160]

Hi Ajax:

Sorry about that -- Assets are on a per shot basis -- if it was an asset that I was going to import from a previous shot or project, I could note that, and when I created the list of assets for that particular shot, I would note that and mark the different steps done.

TIM

[CJ_London]

OK so you don't need a list of assets to select from or predefined for each type of shot.

So don't think I can add any more

you have primary tables of

tblProjects
ProjectPK
ProjectName

tblShots
ShotPK
ProjectFK
ShotName
AssignedTo
DueDate
Layout - populated with state
Camera - as above
Lighting - as above
FX - as above
Rendering - as above

tblAssets
AssetPK
ShotFK
AssetName
AssignedTp
DueDate
Concept
Modeling
Textures
Rigging

and lookup tables for states and steps

you might also need a lookup table for assigned to

with regards notes, you probably need two tables, one for shots and one for assets

the fields highlighted in red you say should be editable - not sure what that means. It may be you just need a single field called 'steps' populated with concept or modelling etc

the ones in green - I assume these are the only steps possible (per the example you provided)

[taholmes160]

Excellent -- Thank you AJAX -- I'll start building the tables hopefully tomorrow -- We have a tree to cut up tomorrow, so I'll see how much time I have

TIM

[taholmes160]

HI Ajax, I didnt get a chance to work on it today, Dad and I had to cut up and clear a wind fallen tree that fell from our yard into the Neighbors yard. The good news is we got it done, but I'm worn out, so hopefully tomorrow -- thanks again so much for the help

TIM

[taholmes160]

Hi Ajax:

Ok, I got the tables created, and got the rough design of the forms -- Ideally the datasheet form to the left and the main form to the right. So that you click on an asset or shot in the datasheet view, and that pulls up the data for the main form on the right.

I dont have the relationships set up, nor do I know how to make the 2 forms side by side or to get them to pull up the correct data

Thanks
TIMDrawingProjectManagement.zip

[CJ_London]

to set up relationships, open the relationships window, click and hold on a PK then drag across to the matching pk in another table. This creates a line to indicate a relationship. Right click on the line to modify the relationship. Google 'access table relationships' to find plenty of links on the subject.

Best practice - Recommend you get these in place, enter some test data and create some queries so you can ensure the tables and relationships work as required before developing your forms. It will save you a lot of time because if you have to change a table for some reason you will need to change forms etc.

To set forms side by side you need to use subforms. Again google to find out more. What links the forms together is the data by way of two properties in the subform control. linkChild and linkMaster. Your PK field is the linkMaster and the FK field the linkChild. If you have created your table relationships then these will be populated automatically when you create the subform control. Another reason for getting table structure/relationships right from the get go

[taholmes160]

Ok, Cool -- thank you very much

TIM