Sculptures

[boundfree]

Hello!

I am trying to design a database (my first one).

Unfortunately for me, the data is not very straightfoward.

My first thoughts were a normal inventory database, but since we are not dealing with 10,000 identical stainless steel scews from one provider, but with unique works of art, numbered, highly valuable, delicate, and subject to all sorts of variations - I knew that simple inventories would not work.

With all the information I handle, I have divided it into 2 working categories, STABLE and VOLATILE. STABLE is that info which doesn't change much, like the sculpture design, dimensions, materials used. VOLATILE is info that changes fast, such as status (in production, in transit, etc). In my situation I cannot let the VOLATILE go, because all current orders are constantly changing and I cannot afford to lose track while I build a STABLE foudnation.

I need the database for many things, at the moment, I am struggling to keep up with the 6-10 different speadsheets, some of which are massive and innacurate. I (ideally) need it to cope with STABLE info such as all the reference info I may need, and then with orders, production, status, invoices...

One of the main technical hurdles for me, for example, is the way these sculptures are uniquely identified, usually with three fields - 1. they have a name and model reference such as "LOVE OF ACCESS in bronze (10cm)" 2. An edition size i.e. out of 20 or 30 - 3. The edition number. - these three fields uniquely identify a sculpture.



One friend suggested that I treat each sculpture as a patient with a personal file, because at any time, any sculpture could be modified for a special client, maybe a base of a different material, a different finish...or sometimes a sculpture gets damaged...or an edition number is swapped...

Yet even this has major weaknesses because at any point a whole edition size may change, or a major model review, since no part of the process is free from artistic intervention, every part of the information processing has to be adaptable...yet I would really like this database to be in charge of orders, production, shipping, invoices - the whole thing...

At the same time, I need to keep this a side project that can run in parallel to our current system as I gradually graft on pieces of information.

I am working on a plan, mission statement, and trying to experiment with access as much as possible. I would greatly appreciate any sharing of information, experiences and ideas for guidance.

Much appreciated,
boundfree

[NoellaG]

Hi,

I think you might need something like a library with books and editions, but in your case with Artworks and editions. I included an example of such a database + a snapshot of the structure (you'll need to refine this of course).

grNG

[rpeare]

Noella has given you a good place to start. I would recommend you consider a few things

First. Because this is a sculpture casting (I'm assuming you're using molds), I would assume you have 'planned' events, basically items that have not been created yet but are due for production/sale. This would also mean that you have 'completed' events. Items that have been cast and are ready for sale?

These can both be tracked in one table, but I would say that until a production run is actually completed you can change ANY of the specifics of the run, the size, the number of items produced, etc.

AFTER the production run is complete you will want an accounting of each item produced. Whether that item is destroyed for a defect (either during manufacture or returned for damage during transit), if it was shipped, if it was donated, etc.

You will also want to account for 'special' add on items (as you mentioned a pedestal of a different model, etc)

You will likely also want a table of all of your customers.

From here you will want a purchase order table, basically a table that assigns a purchase order number with a customer ID field

A purchase order detail table, contains the purchase order ID, and a the available items from a given lot of product. For instance when you have a batch of 20 sculputures of a given model you can assign them a serial number (even if it never appears anywhere on the sculpture, it's just a method for you to track items) which you can apply to any given batch. If you are imprinting a numbering system on the sculptures that's even better but it's not necessary. So if someone orders 5 sculptures from five different lots you can accommodate it.

There's a lot to consider in any sort of inventory management type system (which yours is, even with the unique complexities). But it can be done, it's a matter of how robust you want your system to be.

[boundfree]

Noella, thankyou for the idea, I hadn’t thought of library analogy - it is reassuring to see a relatively simple structure for what could be quite complex.

Rpeare, thankyou for the extremely valuable response, you have seen the situation extremely well.
We do have planned events, the main ones I need to track are ordering, production, completion and shipping. Also yes, after the planned events we have accounting. (note: we have various providers and ways of assembling whatever art)
As you rightly point out we have many special cases – clients with special requirements, repairs, special maintenance notes.
I do not deal with the end customers so that is not an area I am looking to include. We also only deal with one artist and so we don’t need to include him in the database.
The purchase order table is necessary but we are not at that stage yet – at the moment we have a shared excel file (with 100 tabs!), that is difficult to use but works, and is better than nothing. Besides, they would need a very user-friendly face to use Microsoft access.
I think it would be important to consider also the fact that I will be the only person to use the database (although I will not be the only one to benefit) and so all the maintenance/setting up/updating will be done by me. I can’t give all my time to it and I recognize that this may affect how robust my system may be.

Taking this into account, I have designed the structure which is included. I really want to get this right before I begin data entry! Comments extremely welcome!

Thanks

boundfree

[rpeare]

You're on the right track but not quite.

First, always, always, always, have a primary key in your fields that is not subject to change. For instance your MATERIALS table has only one field, MEDIUM. This should have at least 2, MEDIUMID (autonumber) and MEDIUM

Let's just say for the sake of argument that you put in a medium of bronzze instead of bronze. Now let's assume you've added a bunch of records to your PIECE table before you realize your error. If you just change the spelling on your MATERIALS table to bronze you have orphaned (removed any link you had) to items in the PIECE table. Further you want to store the MEDIUMID in your PIECE table, NOT the description field. If you do things this way it doesn't matter if you misspell anything you can correct it or change it at any time and your existing records will not be affected by the change, they will just adopt the spelling of the changed item because you have stored a unique key and not a variable text field.

This goes for all your tables Edition Size, Sizes, Title that I don't currently see a unique key value for.

You have a logic issue with your relationships between these three tables:

BASES
PIECE
ITEMS_ORDERED

Your BASES (many) is linked to your PIECE (one) table
Your BASES (many) is also linked to your ITEMS_ORDERED (one) table on a DIFFERENT field
both your BASES *and* your PIECE tables are linked to your ITEMS ORDERED on *different* fields.

This is a big big problem.

See my thumbnail for a diagram for everything below

I'm guessing that when someone orders a piece they also order the base they want so you may have assembled items that are popular but you will also want to have a list of possible items and their bases with an associated cost for easy reference when you go to create a purchase order.

You will also want the ability to have multiple items per order, the way you currently have it set up you have no way to group items on the same order number.

Please note this is not designed as an inventory management or process management/planning tool, this is, right now, simply a way to track orders of specific pieces/bases as they relate to customer orders.

EDIT: one more thing, there are reserved words in access that perform specific functions, in one of your current tables you have the word 'number' for a field. number is a reserved word and will cause all kinds of problems. The easiest way to avoid this is to use compound words (abbreviated) so instead of NUMBER in the amount order you could have NumOrdered, or QtyOrdered. If you consistently use compound words to make your field names you should never have an issue with reserved words.

[boundfree]

Thanks again rpeare for your help.

I have taken on your advice to always include a autonumber primary key.
Yes, the design I put forward was simply to keep track of orders, not to track production, yet this is something that we would need to track, so I am looking to include a STATUS field somewhere. You mention the inventory/process management and planning tool – I am hoping that adding status/location fields will do - there must be more to it though!

The complication with sculptures and bases is the fact that a sculpture has to be numbered (i.e.1/20) but the base is not. An order may come in for a 3/20, and this will need to be tracked closely, but any base will do. Apart from having variations, such as a client that prefers metal to marble, but at times clients simply do not want a base.

In the relationships diagram included, on the table “tblPurchaseDetail”I have added a few other things 1 Shipments - so that we can track on which shipment an order has travelled, or just track a special shipment. 2. New Pieces – there is another area we like to track which is the development of new pieces (making first models, plasters, modifications, approvals, moulds, etc) 3.Additional Orders – for the extra things that crop up. 4. Status – this is where I guess the Status field would go.

I assume that on tblPurchaseDetail, fields can be left blank, for example, when a New Piece order is made for a modification, most of the fields would be blank. I am having trouble thinking of another way to do it, i'm sure its possible, but I just can't see it at this time!

Something tells me there are a couple intermediate tables missing also.

boundfree

[rpeare]

Since your invoicing is the most important at the moment (at least that's the impression I get) get that part working first. The basic table structure I've given you will not compromise your ability to get the inventory management part working later. Inventory management is a *lot* harder to handle if you're a neophyte to Access. Do not attempt to do a huge project right out of the box, it will only confuse and frustrate you.

My suggestion is to start with something simple, for instance make a data entry form for your base type. Then create a form with a button on it that allows you to open that form and do data entry then move on to the more complex issues like creating a form that allows you to create a catalog (I don't know if you have a catalog but it might be a good idea to create catalog numbers as well so when you get phone calls the customer can reference a catalog number which would make it far easier for you to do searches on your data)