Catalog Design

[manda]

Ok...I'm going to try to find a way to phrase this without (hopefully) just confusing everyone. I just have a few questions about the design I chose for my database. I created a database for cataloging my artwork. The tables I have right now are:

• Name of Artwork (which includes columns for the name, type of work, customer, occasion, occasion, and cost)
• Customer
• Occasion
• New or Copy
• Cost
• Due Date
• Tasks

I broke my tables up like this because I am brand new to Access and it was what made sense to me. The first table basically contains tabs inside of it that are the same as the tables I created. So...I guess my question is, in my first table (Name of Artwork), should it only contain the name of the artwork? Should I delete the other categories inside of it since they have their own tables? And either way, yes or no, my second question is: I haven't started working too in depth with relationships and other things yet, but could someone clarify if my way of thinking is correct: When I get to the point where I am making queries, if I want to make my database so that I can search for the name of a piece of artwork and have not only the name, but all of the other information (customer, cost, etc) come up, then do I make relationships between all of those tables and each individual piece of artwork?

I hope that makes sense....if not, please let me know and I will try to clarify it even more. I'll include a screenshot of my tables just in case it helps. Thanks to anyone who reads for their time!

[June7]

This is for a retail operation selling art?

Why is there a table for DueDate and table for Costs? What is table NewOrCopy for?

Recommend no spaces or special characters/punctuation (underscore is exception) in names.

[manda]

Not exactly. For the database class I am taking, we were told to make a database that would useful or interesting to us. I don't have a personal business or anything, and didn't want to do something really generic such as a database of contacts, so I wanted to catalog my artwork. All my tables are based around personal goals I set for myself; therefore, the DueDate would be the personal date I set for when the piece was supposed to be finished, and Cost would be what the materials had cost me. NewOrCopy would be explaining to myself whether or not it was an original piece created especially for somebody, or if I just recopied a former piece for someone (for example, if someone liked a drawing I had made previously, I would just make them their own copy instead of drawing them a completely new design). Basically all the tables exist as a way of adding information about the artwork that is relevant to me, since the database is essentially for my own personal use. I created tables such as Cost and DueDate because I thought it would be helpful to see how much time and money I was spending on this hobby.

[June7]

Okay, but Cost, DueDate, NewOrCopy are just attributes about the art. Every art will have this info and assuming each can have only one cost and one due date and is either new or copy, separate tables are not required.

Review info on identifying entities of database. I see your entities and their attributes as:

Art
ID
ArtName
ClientID
Occasion
NewOrCopy
Cost
DueDate
Tasks (not sure if should be a related child table of task records)
Clients
ID
LastName
FirstName
Phone
Address

You can have auxiliary tables to hold info that can be used as 'lookups' - sources for restricting choices in comboboxes - such as Type. The choices could be: Ink, Acrylic, Oil, Charcoal, Pastels, Crayon, etc.