Need to make a database/website

Here at the theater, we have 96 years of junk; props, furniture, scenic elements, etc.  Our props rental is usually me or a volunteer fielding phone calls and emails about "do you have any victorian furniture that's kinda red?" or "I'm looking for roman stuff."  Then it's a process of rummaging and guessing what the person means by "shabby chic" or whatever other nonsense term they have used.  It's like if one of you asked me for an opinion on a car and said "I want something really cool and beige or grey."  I can only guess what is cool for you.

Then it comes to price for rental and I'm like "I don't know, dude... $40?"  It is the maximum effort and time suck for the least benefit.  We become the designer/curator.

We are getting offsite warehouse storage for all of our E36 M3.  In the process, I want to photograph and inventory everything and put it on a website.  That way I can direct everyone to the website and they can make a shopping cart of what they want.

I need it to be a big sortable database with categories like furniture, weapons, plates and silverware, curtains, musical instruments, and severed body parts (yes, I have several).  On the left hand margin of nearly every shopping website like Summit, Amazon, or WalMart, there are selections for color, material, price, size, etc.  I want that.  Just like that.

So if someone needs an 80s floral-print couch that is no more than 84" long, they can navigate to furniture > couches and loveseats and then use the radio/selection buttons on the left and enter a max width to narrow it down.

Where do I start?  The inventory part will likely happen before the website part, so I want to be ready to start the database in something like excel and have it be import-able into a website editor? He says hopefully?  I'm so lost.  Our IT guy is great, but he does IT for the Navy, so he isn't up to speed on commercially available options.

You may be able to adapt an ecommerce solution without too much pain. Take a look at Shopify and Magento to start. Shopify you could probably setup without much technical help. If you want to go with Magento you'd want to enlist outside specialist help.

Step 1 is to figure out what attributes you want to store. They're going to be a lot more difficult to add later. Things like age, cost to rent, width, weight, material, etc. All of those little magic radio buttons.

Then you can start with a spreadsheet. It'll be easy enough to import into a database as long as it has the information you want the database to contain.

The hard part is going to be the web interface. Well, not HARD. But it's going to probably involve custom code which means you either need to pay someone or get a volunteer to do it. The prettier you want it to be, the more it'll "cost" in terms of volunteer enthusiasm, money or time. I could bang together a basic HTML version that would be very fast and very not pretty in no time. Think Craigslist, not Facebook Marketplace.

But it's all going to come back to how well you did Step 1. You can't search on attributes that aren't in the database. The right coding could give you the opportunity to add attributes ("we need to sort by smell!") but of course it won't retroactively add them to all the existing products.

You know, a Magento webstore can do this and it's open source. A bit overpowered for what you want to do, but you can assign searchable attributes, inventory, cost, weight, etc. Sign up for hosting on Bluehost.com and I think they've got a script to install it for you.

I know Magento far better than I want to :) A stock install can do what you're asking.

The big commercial websites have a whole bunch of money in their sites, so trying to duplicate that on a shoestring isn't going to be an option.

Even though I do database consulting for a living and my employer offers a free version of their product, it's going to be a fair amount of work to put a front end on a database that's reasonably usable by a non-database person. Putting together the database part that does the import from Excel is the easy part . In other words, you really don't want anything custom here.

What I would look at instead is see if someone has done some sort of inventory management plugin for WordPress and check how usable that would be for your use case. That's very likely to be the most usable and cheapest option for you.

One of the advantages of using something like WordPress or Magento (which is probably better suited to this task) is that you can buy support at relatively reasonable rates.

I do this E36 M3 for a living.  Without a thorough understanding of your business model, and just off the top of my head:

You have props.  Props have traits.

MSTR_PROP

------------------

PROP_KEY

Prop_Name

Prop_Description

Storage_Location

Cost

Rental Fee Per Day

MSTR_Characteristic

------------------------------

Characteristic_KEY

Characteristic_Description

Characteristic_Detail

Props

----------------------

Prop_Key

Characteristic_Key

So, red couch is Prop_Key = 1, 'RED COUCH'.  Prop_Key=2, 'Roman Sword'

Characterics:

1, COLOR, RED

2, COLOR, BLUE

3, SIZE, BIG

4, STYLE, Victorian

5, CLASS, Furniture

6, CLASS, Weapon

7, Size, Small

8, TYPE, SWORD

In Props:

Prop_Key. Characteristic_Key

1, 1

1,3

1,4

1,5

(that described your couch as Big, Red, Victorian, Furniture)

2,6

2,7

2,8

(That described your sword as weapon, small, sword)

Link MSTR_Prop to Props on prop_key, props to Characteristics on Characteristics_Key, search characteristics for what you want.

Anyway, that's just one way to do it and not fully normalized.  You could do it all in a flat table with a whole lot of columns going across, each with a characteristic as well.  Might be simpler for you, but not as flexible.

FYI, if you do go the Magento route, feel free to hit me up via email with questions.

I think the challenge will be in what things you can select.  For instance, the length of a couch might be important, but the length of a legal-size clipboard will all be the same.  Or... the color of a side table is important, but the color of a fake-turf rug is always going to be green.

But I have a long list of possible selectable categories that could exist for everything even if they are irrelevant for certain pieces.

• color
• l x w x h
• material of construction
• weight
• decade/genre
• specific show (which could be multiple... like Audrey puppets only really work for Little Shop, but the prop hairspray cans for Hairspray could also work for Legally Blonde and Steel Magnolias)
• I would like a category for customizable or not - like I have some painted wood furniture that I don't mind if they paint it a different color, but my varnished/natural wood needs to stay unaltered.

There are at least a dozen more, but you get the idea.

I will likely have to enlist the help of a pro since the last time I made a website, it had two pages and was made in 2001 on a free version of Adobe Premier.  I just want to make sure that the monumental task of putting an excel spreadsheet together won't be wasted when it comes time to pony up for the website and my web designer says "uh... can't use .xls stuff."

Excel into database is the least of your problems :)

Ok.  First things first is I need to get access to our website to see what the host has available.  Truthfully, our host kinda sucks so first step would be to get the board to agree to move it somewhere that actually has a shred of bandwidth.

Most hosts have wordpress, so I'll check to see if it would be something that works for me.  If not I'll see if Magento can work with all of you helping.  If it doesn't work, I'll likely hire one of you to do it for me.

Dr. Hess... your idea is similar to what I was planning, but yours is more code-smart I guess.  I was going to assign a serial number to each item that is like a VIN.  the first digit is general category:

1- furniture
2- prop
3- costume

Second digit under each one would be another delineation that applies to everything... maybe color.  Third digit maybe size, etc.  From there, category 1 could be broken down to 1= chair, 2= loveseat, 3= couch, 4= table, etc.

Although your breakdown is much simpler, I'm not sure it would be easy for the user to only be able to select large red couch.  I mean... yes, I only have about 3 large red couches, but if this rental side of the business takes off and suddenly I have 60 of them, it might be nice if they could choose red, under 84", mid-century mod, vinyl.  I'm thinking future-proof, or at least 5-year proof.

I think the real challenge will be when to stop the categories.  If I made a category tree for everything, it would be 20 branches deep.  I'll need to overcome my OCD and just say props > office items and then let the keyword search narrow out the inkwells or the pencil sharpeners.