I am creating a database to control amongst other things the replenishing and money collections from vending machines in multiple locations in sites. I have come to a brick wall over one answer, probably blindingly obvious, but I have been scratching my head over this - can anybody help?
Machines have from 1 to 9 vending columns. Each machine may have different products for each column. A machine may have a money collection cup for each column or a single cup for all columns.
Money is collected from each machine and recorded against each column, except for those machines that have a single undifferentiated cup.
How do I design the database to cover both of these circumstances?
You need 2 tables -- a machineID table and a collections table. The first table stores machineID, other info and has one column that indicates the number of collection cups (1-9, and a value of ZERO if a community cup). The second table needs 12 columns, one for the machineID which links it to the first table, another for the collection date, and 10 currency columns, ZERO through NINE.
The user inputs the machineID, and a list of collections. The front end looks at the first table for the machineID to find the number of columns, then stores the machineID and collection date into the second table before it parsing and storing the list collections based on the number ofcollection cups indicated in the first table.
Thanks for that. Something's flowing - let's hope it's creativity;-)
The thing that bothers me is that this is the most data-dense part of the business. Most machines are between 1 - 3 columns, which means an awful lot of nulls in most records in the Collections table. Additionally, we have to know the stock type from time to time and the amount vended from each column, so (and I'm thinking out loud,here) we could have a columns table to ID each column in each machine, a columns collection table to record cup takings and a column stock table to record stock movement. Each machine could have a dummy 0 column to record global receipts, or we could de-normalize and record takings totals for each machine visit. Machines with a single cup would have no records in the column collections table.
I thought I sent your a reply on Sunday, but I don't see it, so I'll send
Don't worry about the all those zeros (not nulls since you're dealing with numbers not text). I suggest designing your collections table to work with ALL your present machines and structurally modifiable to handle future ones. Personally, I wouldn't store computations in a table, just raw information.
Stock items and quantities sold per machine per unit time information should go into a 3rd table, and a 4th might hold route information.
Here's my first iteration -- I don't know your business models nor am I familliar with UK vending machines (from the sounds of it they are a lot different than ones I've used here in the USA), but here's a stab and 'most normalized' which could be overkill if you don't have it in several buildings/locations/etc.:
syntax is table_name( primary_key, fk_foreign_key, some, other, columns )
The following location info is useful if you (or the business users, who I suspect will at some point) wish to run reports such as "Show me sales figures for all machines in the US" or "How many machines are running low on stock in the 3 buildings furthest away from their supplier?" or "We wish to add a new machine to building XYZ. Where should it go?" -- so you can extract some business intelligence out of the system. Cross referencing selling iformation with building/floor data could indicate that machines on floors 1 and 2 of building XYZ sell out quickly whereas floors 3 and up never sell out, perhaps necesitating the need for more machines on floors 1 and 2.
country stores country information, such as 'UK', United Kingdom; 'USA', United States of America; 'DE', Germany; etc. country( country_code, full_name )
state/province/etc. -- whatever the next logical subdivision of your country could be, before city. Perhaps Zone? zone( zone_code, fk_country_code, full_name )
city stores city information -- most cities in the US are abbreviated with 3 character airport codes, e.g. Columbus, OH is CMH, could easily be a integer ID instead of code city( city_code, fk_zone_code, full_name )
building stores the building information your machines live in (if they are spread out over several buildings/locations, not sure about your set-up). building( building_id, fk_zone_code, street_address, name, ... )
floor -- stores floor information for each building floor( floor_number, fk_building_id, other_information, ... )
//room -- are your vending machines stored in rooms or just sitting around outside floors?
vending_machine -- holds the name (or some other unique identifier) and location of the vending machine vending_machine( vending_machine_name, fk_floor_number, name, serial_number, model_number, etc.. )
Column in the machine, capcity in number of stock units (I assume they are interchangeable) vending_column( vending_column_id, capacity, ... )
vending cup would store how much $$ it can store, I suppose, or other info you might want to know (I assume there would be vendor info stored in another table so that if the vending cup breaks you know who to call). vending_cup( vending_cup_id, capacity, ... )
stock stores type of stock, creame cake, pretzels, etc. stock( stock_code, name, unit_price, etc. )
Which machines have which columns? machine_column( vending_machine_name, fk_vending_column_id )
Which columns have which stock and how many units? column_stock( fk_vending_column_id, stock_code, amount_left )
Which columns have cups? column_cup( fk_vending_column_id, fk_vending_cup_id )
Which machines have cups? machine_cup( fk_vending_machine_name, fk_vending_cup_id )
Just a post to apologise for my delay in thanking you both for yours. I've just got back from a business trip an haven't slept for the last 26 hours. When (if?) I surface I'll work through the suggestions. Good forum!
vududoc, can you explain your point about nulls? I've always regarded nulls as an absence of data of whatever datatype.
Assume the collections table has 10 currency cups (0 through 9) and the machine listed there has only 3 cups, then columns 0 and 4 through 9 would not contain data. If you place zeros, not nulls, in the unused columns, you can easily do all sorts of computations ACROSS columns and down records, and you won't have to worry about handling future changes in the number of stock columns in a machine. That's what I was hinting at.