Art Gallery Database

[Mike Monville]

Art Gallery Database:

I have built the following:

Member table storing member data: MemberId (autonumber), FirstName,
LastName, address, media, commissions?, membership type, etc.

Inventory table storing data on artwork in the database: ArtId (autonumber),
MemberId, TagNo, Title, Medium, Price, date in system, ShowId, etc.

Show table storing data on individual shows: ShowId (autonumber), ShowName,
StartDate, EndDate, Commission, EntryFee, Juried?, etc.

Sales table storing data related to sales: SaleId (autonumber), SaleDate,
BuyerName, BuyerAddress, etc.

The Member table and Inventory table are related through the MemberId field
and work fine.

Here are my dilemmas:

I want to be able to keep a given piece of art in the inventory table until
it is sold because many times art will be re-entered in another show and it
will not have to be re-entered in the database if it stays in the inventory
table. I built a "select" bit in the inventory table to control whether
that particular work is in a show or not. In order to keep the work in the
database forever, the ArtId (an autonumber field) will keep growing and this
reference number which will also be posted on the artwork by way of a tag,
will eventually become too large to be manageable. Is there a better way to
handle this or maybe starting over with the autonumber field down the line
may not be the nightmare that I envision.

I can't seem to get the sales table to relate to the inventory. I would
like to be able to design a sales form that would have a subform that would
list each item (ArtId) purchased and perform tax calculations, artist and
commission proceeds, etc. on each item as well as totals for the sale. I
built a salesdetail table to join the inventory and sales tables but when I
create a form and try to use it, it tells me "Cannot add record(s); join key
of table 'inventory' is not in the recordset". I don't understand the
message.

I have not yet made a relationship between the shows table and the inventory
table

This is my first experience with database design so I can use any advice you
can give me.

Thanks - Mike

 

[John Nurick]

Hi Mike,

Comments inline.

Art Gallery Database:

I have built the following:

Member table storing member data: MemberId (autonumber), FirstName,
LastName, address, media, commissions?, membership type, etc.

Inventory table storing data on artwork in the database: ArtId (autonumber),
MemberId, TagNo, Title, Medium, Price, date in system, ShowId, etc.

I'd call this Works rather than Inventory, because it's not an
inventory, it has one record for each work that passes through the
gallery.

Show table storing data on individual shows: ShowId (autonumber), ShowName,
StartDate, EndDate, Commission, EntryFee, Juried?, etc.

If an artwork can be included in more than one show, you have a
many-to-many relationship between Works and Shows. Drop the TagNo, Price
and ShowID fields from the Works table, and create a new table to
implement the M:M relationship:

WorksShows
ArtID - foreign key into Works (ex Inventory)
ShowID - foreign key into Shows
TagNo - tag number put on this work in this show.
Price - price may be different each time a work is shown

Sales table storing data related to sales: SaleId (autonumber), SaleDate,
BuyerName, BuyerAddress, etc.

If you have repeat business (and I hope you do!) it's probably worth
having a Buyers table, with just a BuyerID field in the Sales table. For
that matter, what if a Member wants to buy something? Maybe there should
be a Persons table containing Members, Buyers and potential buyers?

The Member table and Inventory table are related through the MemberId field
and work fine.

Here are my dilemmas:

I want to be able to keep a given piece of art in the inventory table until
it is sold because many times art will be re-entered in another show and it
will not have to be re-entered in the database if it stays in the inventory
table. I built a "select" bit in the inventory table to control whether
that particular work is in a show or not. In order to keep the work in the
database forever, the ArtId (an autonumber field) will keep growing and this
reference number which will also be posted on the artwork by way of a tag,
will eventually become too large to be manageable. Is there a better way to
handle this or maybe starting over with the autonumber field down the line
may not be the nightmare that I envision.

If you want each work to have a unique reference number you'll have to
put up with numbers that may run into several digits. But as shown above
this doesn't mean you're lumbered with inflexible lengthy numbers when
hanging a show.

I can't seem to get the sales table to relate to the inventory. I would
like to be able to design a sales form that would have a subform that would
list each item (ArtId) purchased and perform tax calculations, artist and
commission proceeds, etc. on each item as well as totals for the sale. I
built a salesdetail table to join the inventory and sales tables but when I
create a form and try to use it, it tells me "Cannot add record(s); join key
of table 'inventory' is not in the recordset". I don't understand the
message.

The SalesDetail table needs something like these fields:
SaleID - foreign key into Sales
ArtID - foreign key into Works
Price - hopefully this will be the same as the catalogue price
(i.e. the price for this work in this show in ShowsWorks)
but all too often it won't be!

I have not yet made a relationship between the shows table and the inventory
table

See above.

This is my first experience with database design so I can use any advice you
can give me.

If this is for a real gallery (as opposed to a learning exercise), you
may also need to consider

-How to handle part exchange and similar deals.
-Suppose the artist modifies a work between two shows, or decides to
show it with another title. Do you just make the necessary changes in
the Works table, or do you need to track these changes so you can tell
in future know that the work was in different states, or had different
names at different times?
-and no doubt much else!

 

[Mike Monville]

John -

Thank you for your response. It made me think about other aspects I should
consider (Persons table, etc.,) while I'm designing the database.

I am still floundering at a basic level however, and should tell you that
this is a real gallery that is currently presenting it's first show and I
have entered 1022 works in the database so far. There are also 90 artists
built in the member table. I would have liked to have had more time to get
all the tables and relationships, etc., built before I entered data, but I
did not have the time to do so. The one luxury that I do have is that we
are tracking all aspects of the show using the old, reliable - but time
consuming manual methods. This gives me a good comparison to build the
system that gives us what we need now, while envisioning and building for
our future needs. Hopefully all the inventory, sales, payments, taxes and
membership info will all be the same at the end of the first show.

Are there inherent problems in reworking tables and relationships since I
now have live data in the system?

I have made the changes to the inventory table that you suggested; renaming
it "Works" and moving the Price, TagNo and ShowId fields to the new
"WorksShows" table. Is there an easy way to migrate the data contents from
the old tables to the new? I assume that this would be the next step in
getting the tables set up and working. I'm using copies of the original
tables/data so that I have something to go back to if I screw things up.

I'm still having trouble getting the Sales table to relate to the TagNo of
the items being sold. I built SalesDetail with: SaleId from Sales (PK),
ArtId from Works (PK), Price (aren't I duplicating data though, since Price
is an existing field in WorksShows?). I want end up with a sales form that
allows the user to enter just the TagNo and have the form populate the
Artist, Title, Price and prompt for the next TagNo(s)., and then total the
sale, etc.

Thanks again - Mike

 

[John Nurick]

John -

Thank you for your response. It made me think about other aspects I should
consider (Persons table, etc.,) while I'm designing the database.

I am still floundering at a basic level however, and should tell you that
this is a real gallery that is currently presenting it's first show and I
have entered 1022 works in the database so far. There are also 90 artists
built in the member table. I would have liked to have had more time to get
all the tables and relationships, etc., built before I entered data, but I
did not have the time to do so. The one luxury that I do have is that we
are tracking all aspects of the show using the old, reliable - but time
consuming manual methods. This gives me a good comparison to build the
system that gives us what we need now, while envisioning and building for
our future needs. Hopefully all the inventory, sales, payments, taxes and
membership info will all be the same at the end of the first show.

Are there inherent problems in reworking tables and relationships since I
now have live data in the system?

It's almost always possible to move data from an old flawed structure to
a new better one, though sometimes a little ingenuity is needed.

I have made the changes to the inventory table that you suggested; renaming
it "Works" and moving the Price, TagNo and ShowId fields to the new
"WorksShows" table. Is there an easy way to migrate the data contents from
the old tables to the new? I assume that this would be the next step in
getting the tables set up and working.

Use Append queries.

I'm using copies of the original
tables/data so that I have something to go back to if I screw things up.

I'm still having trouble getting the Sales table to relate to the TagNo of
the items being sold. I built SalesDetail with: SaleId from Sales (PK),
ArtId from Works (PK), Price

That seems right to me.

(aren't I duplicating data though, since Price
is an existing field in WorksShows?).

The Price field in WorksShows stores the asking price for that Work in
that Show, while the Price field in SalesDetail stores the price for
which it was sold. From what I know of galleries, these aren't
necessarily the same thing, so there's no redundancy here.

I want end up with a sales form that
allows the user to enter just the TagNo and have the form populate the
Artist, Title, Price and prompt for the next TagNo(s)., and then total the
sale, etc.

It's an indirect relationship: the form has to use TagNo and ShowID to
look up (in WorksShows) the ArtID and Price that go with that TagNo.
Then the ArtID is used to get the Title from the Works table. Typically
one would do this with a query that joins the three tables and uses the
values of ShowID and TagNo as criteria.

 

[Mike Monville]

John -

I have made a lot of progress on this project by following your advice, but
alas, I have come to another dead-end and can't puzzle my way out of it.

Following the Northwind examples, I built a combo box that takes the TagNo
from the user and returns the Title, Price and Artist in a subform based on
a select query but now how do I use that data to "make" the sale; i.e..,
populate the DateSold field in the Works table and then prompt for the next
TagNo, and close out the sale by totaling, etc.

Mike

 

[John Nurick]

Mike,

As I understand the situation, you need a form bound to the Sales table,
and on it and a subform bound to a query that joins SalesDetails and
Works (and Members?) to return SalesDetails.SaleID, SalesDetails.ArtID,
SalesDetails.Price plus Title and the artist's name from the other
table(s). Form and subform are linked on SaleID.

To add a work to a sale, I'd probably use a button on the subform's
header or footer (maybe on the form itself)

Clicking the button would open an unbound form in dialog mode with a
combobox so the user could select the work to add. The combobox's
RowSource would be a query that returns all the TagNos from the present
show that have not yet been marked as sold, along with their titles and
prices.

Clicking OK on this form would run code that builds and executes a SQL
INSERT INTO ... VALUES statement (single-record append query) to create
a new record in SalesDetails and then requery the subform and update a
textbox in the footer of the subform (or on the main form) that shows
the total price so far, tax and so on.

At any point the user would be able to adjust the prices in the subform.

Another button would close out the sale (printing the invoice and
whatever else you need).

In strict relational terms you don't need a DateSold field in Works; if
you want to find out whether and when a work has been sold you query
SalesDetails for a record with the relevant ArtID. No record means it
hasn't been sold. But you may want one for "audit trail" purposes: which
brings us to another whole difficult area. How secure does this database
need to be? Does it matter if the user can easily go back and change
previous records?

 

[Mike Monville]

John -

Thank you for your help thus far. I am now back to the project after a
Thanksgiving recess.

Could you be more specific regarding your response: Clicking OK on this form
would run code that builds and executes a SQL INSERT INTO ... VALUES
statement (single-record append query) to create a new record in
SalesDetails and then requery the subform and update a textbox in the footer
of the subform (or on the main form) that shows the total price so far, tax
and so on.

I am trying to learn VBA while building the database and this code is
currently beyond me and I don't really know where to start. Any help would
be appreciated.

thanks - Mike