Showing posts with label database. Show all posts
Showing posts with label database. Show all posts

Friday, 23 October 2015

The crushing inevitability of MiData

Yesterday there were news stories about how the average UK consumer could save up £70 a year by changing bank accounts. Unbeknownst to me, the government had launched an initiative months ago, urging banks to allow customers to download their bank account transaction history in a standard format, namely MiData.

MiData is a comma separated value text file, you can open it in NotePad or Excel.

At the moment pretty much the only two things you can do with MiData is faff about with it in a spreadsheet, or upload it to GoCompare who will somehow process it and tell you which bank to change to.

I think GoCompare just looks how far into your overdraft you go and what the average account balance is, they then look at which bank accounts charge and pay what interest and other goodies and make recommendations. Their best recommendation for me was some Yorkshire bank who charge higher interest, but give you a £150 switching bonus, so less of a saving, more like a one-off free gift.

Anyhoo, there's so much more potential and risks involved with MiData.

Years ago I read online, possibly from Worstall, of an idea for banks (with the user's permission) to mine your data and automatically save you money by changing various service providers. For example say your current energy provider charges £30 a month, but other people in your area with the same household size are only paying £20 with a different provider, then the bank would change you over, saving you £10 a month. Presumably the bank would pocket half your saving for a limited period, but since you're paying less, who cares. No bank has done this, probably because of privacy laws.

With MiData, the ability to minedata is outwith your bank. But at the moment, there are no tools, no services. The main risk is that the MiData is just too personal.

When your bank lets you download the MiData, it is "anonymised" which by the looks of things means they remove any account numbers, and anything that look like an account number, just replacing it with asterisks. This only makes it anonymous in that you don't know personal account details, but that's not enough.

As an aside, I understand that some car insurers fit a black box that records your car's speed and time, so that they can insure you appropriately for how safely you drive. I read that some researchers can use this speed data alone to figure out where you are going each day. It takes a bit of datamunging, but presumably if you know the start point and the junction one way is 30 seconds drive and the junction the other way is 50 seconds drive. Any nefarious criminal can map your life just from speed measurements.

Similarly, from MiData, even without account details, it would be trivial to identify a person from their transactions.

For example, looking at petrol stations and supermarkets you can get a feel of where in the UK a person lives and works, they'd do their weekly shop within one or two miles of their house, their regular petrol fill up will be somewhere between their home and their place of work. Or even better their local train station or work train station will be within less than a mile. Occasionally they will be travel or petrol transactions further away, these would be holidays or visiting family members, traditionally some family members stay in the same place where they grew up. Likewise gift purchases will coincide with birthdays. An investigator can get themselves to Linkedin and Facebook and look for people who live in this area, work in another area and grew up some other specific place, and who's partner / parents have birthdays at whatever time of year.

There aren't many people who live in Chingford and work in Hertford, even fewer who grew up in Manchester.

Anyhoo, the cat is out of the bag. Like in the book The Light of Other Days by Stephen Baxter and Arthur C. Clarke, the post-millenial generation aren't going to give a crap about privacy, compared to the "benefits" of datamining. I imagine that security expert Bruce Schneier would be doing his nut in.

So, having identified a gap in the market, I have an awesome idea for a business that will turn me into the millionaire I've always dreamed of being.

First we create an app or website where people upload their MiData to and the site gives you a neat pie chart showing how you spend your money in categories like supermarket, petrol, Entertainment, etc, and histograms showing how much you spend on each category each month. Just like Quicken used to do before they discontinued the UK version.



Don't worry, your data has already been anonymised by the bank, the government said so.

Then once we have enough people's "anonymised" data, we add some data, like geographic locations for each supermarket, train station and petrol station and cafe, then we offer website users a fancy map showing where they spend. People will think its ace, and Bruce Schneier will start getting worried.

Then we do some more analysis showing how much people in different areas are spending on things, like the aforementioned energy providers, and we can start charging users for recommendations for where to switch to.

Then we can start telling people how many kids we think they have based on their data, and how many bedrooms their house has, recommendations of which car they should buy next, which phone and whether they are engaged in illegal activity, or what things they do that are abnormal.

The problem is that I don't have time to do this, neither have I the skills. Someone else will.



The government will at the same time as encouraging it and providing grants to organisations who can take advantage of the MiData, will also have very legitimate concerns about privacy.

There is a very faint trend on social media for young people, teenagers who have just received their first ever credit card, to post photos of said card and unwittingly give away the security number, so that nefarious people will use their details. Young people can be stupid. Lots of people are stupid and will do stupid things.

The government, and parents too, have a difficult job in weighing up the benefits of things like MiData and credit cards, with the risks. What protective measures will they put in place that are just as much of a ballache as the EU Cookie Directive, that makes you have to click on disclaimers on websites.

Imagine, if you will legislation that protects people's MiData privacy by putting in place some hardcore digital rights management, only allowing special government approved organisations and businesses to view and process, thus no small app developer could play with the data, only GoCompare and the banks and probably government departments, HM Revenue & Customs, and the police, probably hospitals too. Some DRM system that's so encrypted and heavyweight that developers often do raw datadumps, and leave hard disks and DVDs on trains.

This is what happens.

Saturday, 20 November 2010

Real users

The other day I was reading an interesting article from the depths of the internet about 'real users', the vast majority of computer users and how they are different from the rest of us.

It was from 2003, some website developer was talking about how on his ISP's home page there was a link to a 'search' page, which had a list of links to that era's top search engines, like Yahoo, and MetaCrawler and so on, sites that have all but disappeared now. The article went on to say that when it was time to revamp the site, the developer got rid of the search page and just replaced it with a google search windowy thing or something, if people really wanted to use Yahoo, or Metacrawler they'd know the URL by now and could just type that into the address bar.

When they changed the website, there was a avalanche of complaints from customers who liked the old 'search page', and who couldn't find Yahoo anymore, it was like it had ceased to exist. The developer couldn't quite understand it, but it seemed the vast majority of people who used their ISP, had set the ISP homepage as their homepage and were otherwise completely lost if they couldn't just click through to search.

They are the real users of computers, people who don't quite understand the way it works or even think they need to understand they way it works.

In the comments people added their own stories of woe, stories of encounters with 'real users' who used computers every day, but didn't quite 'get' it.

Like people who don't use the address bar in their browser, they only ever get to things through having google as their homepage, and just entering everything there. The very existence of a URL is a mystery to them. This still happens, in 2008 the technology blogReadWriteWeb was lamenting how the vast majority or respondents in some market research don't use the address bar.

Even in February 2010 ReadWriteWeb did an article that had the words Facebook and Login in the title, magic google juice flowed and it became the top result when you search google for 'Facebook login'. People arrived at as site that wasn't Facebook and complained they couldn't login, three thousand or so people leaving comments along the lines of
Ok If I have to I will comment,I love facebook so right now just want to log in if thats ok with you..lol Keep up the good work...
They were completely lost.

I've grown up with computers and the internet, I have a little bit of a blind spot for how other people can not have the same understanding. The other day I was asked "What's the difference between Chrome and the internet?" Sure there are gentle answers to such questions, but all I could do was pat their hand and say "Don't you worry your pretty little head."

'Real users' get scared on Microsoft Word when you put on to make nonprinting characters visible. They're having problems with the layout, so you click the icon to see what's going on, and suddenly they protest, "No, I don't want that!", so you patiently click them off, and mystically explain that there's a few tabs there and a section break where it shouldn't be. The 'real user' looks at you in utter disbelief, wondering how you can possibly know such things.

There's something in that "No, I don't want that!" cry, it reminds me of the sort of thing my three year old niece would say.

'Real users' get confused when you move the icons around on their desktop, or if an icon gets obscured by some other window in front of it. If they can't see it, it has ceased to exist. Its like playing peekaboo with a six month old nephew "Where's teddy gone?", "There he is... behind the cushion that I just put in front of him."

Hmm, a six month old can figure this out, why can't 'real users'? What's wrong with them?

I kind of understand, in the early eighties, about eight hours after we first acquired an Amstrad CPC 464, I remember my brother playing 3D Monster Chase, I watched him for hours, my brain/eyes couldn't understand what was going on on screen, its seemed like some kind of coloured bowtie matching puzzle game, rather than the low-tech Doom clone game that it was. It was a few days before I could comprehend the image as a 3D scene.

'Real users' haven't had that epiphany yet, of realising that Windows are representations layers of panes that can be moved about. 'Real users' don't get that nonprinting characters are helpful and don't print.

The thing is, 'real users' can be spectacularly successful in their endeavors. Just pressing enter lots of times to get to a new page in Word doesn't seem to affect the success of their careers. Sure it may mess up the rest of the document if you need to change something on the first page, but that can be fixed easily.

If people knew about manual page breaks then documents wouldn't need to be 'fixed easily'.

The success of Windows and Vista comes down to making computers dead easy to use, so real users can get churning out poorly laid out documents quicker, without having to learn the ropes. Google's success was in getting to porn just by typing the one word then clicking, without having to remember any of that "http://www." business.

As someone who isn't a 'real user', a geek possibly, it dismay's me that rather than making 'real users' understand things and expand their mind's instead life just gets easier for them.

But no no, the vast majority of people are real-users.

In most offices, when you stand up and look around, most people you see are real users. They're working hard, getting stuff done, but they don't know what they're doing really.

Monday, 26 April 2010

Opting out

This morning I was trying to think of example reasons for why you'd want to opt out of having your medical records in the NHS's database thing, and whilst I could come up with many theoretical hypotheses, the only concrete examples I could come up with was the possibility of your details being leaked or lost or left on a hard disk on a train along with a few hundred thousand other people's details.

Its not really that convincing, cos in such a case there would be a few hundred thousand other people in the same boat and there'd be a case for some kind of class act.

However, via Dizzy Thinks, we find that Greenwich Council are cross referencing their payrole records with council tax and housing records to see if any of their own staff owes them any money. They have to inform the staff that they're doing such a thing.

Its probably not quite what the council employees had in mind when they took up their jobs, that their employer would be seeking out new and exciting ways of extracting money from them.

So aye, at some point in the future, the NHS could decide to mine their records for anything that could be used to extract more money from you, which is why its wise to opt out.

Saturday, 12 September 2009

The Independent Safeguarding Authority and judgement

I'm am not a parent, I have little interaction with children, except for my niece who I have all the time in the world for. But I used to be a kid myself, and remember lifts to scouts, swimming, and the occasional school run.

I'm wondering, in the post-peodophile future, when all our children are safe, next month in fact, what happens if parent no. 3 on the giving lifts rota fails the ISA check? We could be liable for a fine of £5,000. I guess if we all chip in its only a grand or so each, or divvy it out proportionate to the number of children, sorry parent no. 2.

I mean for the past X years he's given the kids a lift to scouts/swimming/school and never fiddled with any of them (we checked), but for some reason the Independent Safeguarding Authority have decreed that he can't be trusted with kids, but well, he's not in prison so he's atoned for any sins, and they're not going to arrest him of anything. Do we still keep him on the rota?

1) We could boot parent no. 3 off the rota, to protect our own kids, just in case and avoid the fine. But then that means the others on the lift giving rota have to do more lifts, and what about his own kids, well, we can boot them off the rota cos they were little brats, but our own hypothetical kids seem to get on well with them. It seems a shame, but its for their safety.

2) Do we stop taking the kids to scouts/swimming/school, its more hassle than its worth and they prefer staying in with the xBox 360 anyway. We can just send them on the bus to school, they'll be fine on their own.

3) Do we trust our own judgement on the issue and keep parent no. 3 on the rota, doing the lifts as he's always done. Cos after all we know him far better than the ISA. The ISA have been around for a few hours now, we've known him since school, and since he first met his wife, she was fourteen, he was eighteen, there was that controversy, oh hang on, ahh. Still they've been together for years now and seem quite the happy family, and well we are responsible adults with our senses intact.

4) Is there some way perhaps to appeal the ISA decision? Is it going to involve weeks of forms and emails and letters and hassle? Possible some kind of sworn testimonial which will involve a day off work or handing over more personal information to the some independent authority who have a track record of handling personal information?

Wednesday, 20 May 2009

Baws deep in SQL once more

So, I've installed XAMPP and I'm pretty sure it all works. I've got PHP & MySQL for Dummies on my lap and a spreadsheet with all the MPs expenses and majorities data.

I used to dabble with SQL in my last job, using Open Office Base to try to create an MRPII system, but that was six months ago, things are different now.

So I've got me one new table, which looks a little like this:-

ID
Name
Attribute
Value
1
Afriyie, Adam
Constituency
Windsor
2
Burns, Simon
Constituency
Chelmsford
3
Cameron, David
Party
Conservative
4
Streeter, Gary
Constituency
Devon, SW
5
Abbott, Dianne
2005 Majority
7427
6
Wyatt, Derek
Expense fiddle link
http://www.telegraph.co.uk/news/newstopics/mps-expenses/5330808/Labour-MP-Derek-Wyatt-billed-75p-for-scotch-eggs-MPs-expenses.html

I think keeps it all kind of simple, rather than having to add columns or create new tables everytime something new happens. Its probably some technique they teach you in first year database management, I swear I read some techy article about this sort of way of arranging databases years and years ago.

Anyhoo, whilst its easy to do a wee
SELECT Name, Value FROM mpinfo WHERE 'Attribute' = 'Constituency' ORDER BY Name
or
SELECT Name, Value FROM mpinfo WHERE 'Attribute' = 'Party' ORDER BY Party, Name
to get lists of each homogenous atrribute. I'm wanting to display a table which has columns for 'MPs name', 'Constituency', 'Party' and whatever else.

Lazyweb, can you help?

It should simply be a case of joining the results of the two previous SQL queries
SELECT Name, Value FROM mpinfo WHERE 'Attribute' = 'Constituency'
LEFT JOIN
SELECT Name, Value FROM mpinfo WHERE 'Attribute' = 'Party'
ON Name=Name
But that's clearly not going to work, so can I run an SQL query that creates two new tables, one for each of the previous queries, then joins them, spits forth the desired information, and then drops the tables.

And can I do this within phpMyAdmin?

Tuesday, 3 March 2009

Band nationalities


Following on from today's earlier post about Gaza, and the tacked on suggestion that Gazan could all form bands and sell music on iTunes rather than rely on international aid or persuading their neighbours to let them export goods, I got into a discussion on twitter about how to search iTunes.

It seems there's no way to search for music by country, and nationality isn't a ID3 tag on MP3s. So I'm going to have to build my own database of bands and their nationalities. I'm hoping to get a list of artists from last.fm, via scraping or benevolence.

Whilst I love building databases, I think for this one I need to get away from using OpenOffice Base, and venture into the world of online databases. MySQL is an obvious choice, but I want to start plugging in the data now, so I'm using google docs spreadsheet.

Check it out here
to see how I'm getting on.

I'm starting with the most popular bands and working down, they seem to mostly be from the UK and the USA, but I'm sure I'll find those Gazan bands in no time.

Ooh, you can embed forms into websites and it populates the spreadsheet automatically, that's worth a try.

Tuesday, 27 January 2009

Product development

I rather like this wee graph that I found on Blue Matter here, not for the way it shows the adoption of new services and technology has speeded up.

But because it lists the trappings of 21st century civilisation, and show's how the western world has spent a century developing these things, making them affordable to the massed, ubiquitous and available.

So in third world countries, for the poor populations, for all the abuse and exploitation that goes on by the west and by their own corrupt leaders, we can take them from the top left corner to the bottom right.

No need to re-invent the wheel.

Is the touchscreen smartphone the pinnacle of human civilsation? The iPhone, is that it? The moment when every member of the human race can have a touchscreen smartphone and the infrastructure is in place to support it, that's the peak of the machine-age.

**UPDATE**
Eep, I forgot about the multi-touch touchscreen smart phone, when that's ubiquitous, then we've won.

Monday, 29 December 2008

Scenester Scene #1

I'm been wondering about how to map the music scene for a while now, its been lurking at the back of my head for month, only bubbling to the forefront on talking to muso-types. But it finally formed a shape in ma heid last night, driving home in a flu-conceived delerium.

The invocation of Venn

Any band can get their own gig, but its the promoters who make the scene, promoters putting on bands who've already passed the hurdle of convincing another person to put them on. 'Music scene' is very subjective, but for me it means the gigs I go to. For someone else its means the gigs they go to. Back in Glasgow it would be The Winchester Club, Drive Carefully Records and Pin Up Nights, but here in London I habitually go to Twee As Fuck and Lost Music, with occasional ventures to White Heat, Goonite, HDIF and a few others.

These promoters are pretty good a picking bands that I enjoy seeing, and occasionally different promoters have the same bands playing, its like a stamp of quality for the bands.

By my reckoning Twee As Fuck have had 60 different bands playing and Lost Music have had 72, the two overlapping by 12 bands.

scene01

Bands who've played both Twee As Fuck and Lost Music (12 of them)
Hong Kong in the 60s
Little My
Mexican Kids At Home
Mono Taxi
Pocketbooks
St Christopher
Stars of Aviation
Still Corners
The Hermit Crabs
The Just Joans
The Pains of being pure at heart
The School

Bands who've played Twee As Fuck and not Lost Music (48 of them)
A Classic Education
A Smile and A Ribbon
Afternoon Naps
Bears
Bearsuit
Betty and the Werewolves
Bricolage
Comet Gain
Connan and Moccasins
Esiotrot
Fanfarlo
Hands on Heads
Hatcham Social
Hotpants Romance
Iguana Nights
Ipso Facto
Je Suis Animal
King and the Olive Fields
Liestenstein
Lis-Bonne
Little Things
Love is all
Monade
My Sad Captains
Peter Parker
Sexy Kids
Shragg
Strange Idols
The Bobby McGees
The Bridal Shop
The Chiara Ls
The Crisps
The Duloks
The Felt Tips
The Inconsolables
The Mai 68s
The Mare
The Margarets
The Noughts and Crosses Band
The Voluntary Butler Scheme
The Wave Pictures
The Winter Club
Theoretical Girl
Tom Hatred and the Angry Band
Vic Godard and Subway Sect
Wake the President
Would-be-goods
Zoey Van Goey

Bands who've played Lost Music and not Twee As Fuck (60 of them)
Air Formation
Autocolt
Champion Kickboxer
Charlie Don't Surf
Daniel Benjamin
Downdime
Dyrain
Falling out of cars
Havana Guns
Horowitz
Indurain
Irene
Jesus Licks
Model Morning
Molloy
Mr Solo
Nat Johnson
Navvy
Pete Green
Phil Wilson
Plans and Apologies
Radio Luxembourg
Santa Dog
Sarandon
Secret Shine
Silver Springs
Slow Down Tallahassee
Smokers Die Younger
Sparkys Magic Piano
Strawberry Story
Swimsuit Issue
Tall Poppies
The All New Adventures of Us
The Argonauts
The Arndales
The Candy Twins
The Cut Outs
The Darlings
The Delanies
The Electric Pop Group
The Fischers
The Gresham Flyers
The Hillfields
The J-Pegs
The Keith John Adams
The Kick Inside
The Lieutenants Mistress
The Lodger
The Loves
The Manhattan Love Suicides
The Night Jars
The Postcards
The Puncture Repair Kit
The Sailplanes
The Scaremongers
The Social Services
The Trudy
The Understudies
Their Hearts were full of Spring
Time. Space. Repeat

You can view all the raw(ish) data here on Googledocs
Well, this wee data analysis has been fun and informative, I'm sure you'll agree. Hasn't really helped me on the job front, and now this flu has my eyes streaming as well as my nose. There's no hope for me.

Other promotions I need to parse in the future
Fortuna Pop (done)
Spiral Scratch (done)
Offbeat
HDIF
Guided Missile
White Heat

If there's any bands I've missed or promoters I've forgotten, let me know. I see this one rolling and rolling

Monday, 8 December 2008

ID

Rather fine video from the No2ID people, as a retort to folk who think that with ID cards if you're innocent you've got nothing to fear.

Tuesday, 30 September 2008

SQL queries

Oh woe is me, I know more about SQL than anyone else in the building, and I'm stuck. It can't be this difficult can it?

Can it possibly be the case that there is a minimum IQ below which SQL is witchcraft, and that this IQ level is something like the 99th percentile? That less than 1% of the world's population are smart enough to comprehend SQL?

There's my table:-

It says that on this date, for this part number, a quantity of this many were 'O' (ordered) or 'I' (received at goods in).

What I want is a wee query which tells me how many of each part number are still on order.

I can do this:-

Which neatly tells me how many of each part I've ever ordered and how many of each part I've ever received at goods in. I just can't figure out how to subtract one quantity from the other.

I'm stuck.

This isn't a puzzle like a Rubiks Cube where there is definitely a solution, this is a mystery where is possible that there is no way I can ever know the answer, no way I could ever have gotten to this stage in my life with the knowledge of how to find the answer, no way to save the company, and destined to have ever shorter and shallower relationships with women until the day I pass away.

Having sufficient SQL knowledge isn't in the usual Manufacturing Engineer's skillset, if I'd spent sufficient time learning SQL to have the answer, I wouldn't be a manufacturing engineer, I wouldn't have this job and so this problem would never have come about, etc.

Any help with these problems greatly appreciated, will pay for dinner.

*UPDATED*

I'm getting there slowly, in small bite-sized chunks

This is my query for listing parts ordered:-
SELECT "PARTNO", SUM( "QUANTITY" ) AS "QUANTITY ORDERED"
FROM "INVENTORYOP"
WHERE ( ( "FLAG" = 'O' ) )
GROUP BY "PARTNO"

And this is my query for listing parts received:-
SELECT "PARTNO", SUM( "QUANTITY" ) AS "QUANTITY RECIEVED"
FROM "INVENTORYOP"
WHERE ( ( "FLAG" = 'I' ) )
GROUP BY "PARTNO"

Now I just need to somehow put them into subqueries and subtract one from the other, aye?

Wednesday, 24 September 2008

406


Spent some time on the Northern Circular Road this morning, it makes me think of ex-girlfriends more than usual. Sad thoughts.

Why am I here now, and not with them? What went wrong? What did I do wrong? and how come other people do the same and it's right for them?

How come I can barely muster relationships lasting as long as four weeks now?

Starting to have teh fear for my birthday in December. How can I evade depression (the natural human emotion, not the serious mental illness which I would never consider making light of in a flippant blog post)? Resign myself drinking wine alone in my room with a packet of chocolate digestives, or make an efford to dig up and re-engage old friends to drink with (seems a bit mercenary to me). Not a chance in hell of having a party at the flat.

Last year was a bit of luck with mental judo and getting to Glasgow, but its too far away now, the light fading.

Thirty years for this?

Saturday, 20 September 2008

Sapp


Everything is covered with dust here, great ideas buried under receipts and post-it notes.

There was a plan to set up a Deep Fried Wolfknuckles covers band called The southside Wolfknuckles. We'd do covers of original Wolfknuckle covers and rockabilly versions of your favourite indiepop hits, I'd recruit members from friends in the nearby indiepop scenes. It never happened though.

I searched the internet, but could fine none, so I asked on Anorak for the chords for The Smittens Sapphire, and duly posted, I tried to record me playing it.

I'm not 100% sure what Soundcloud is, but there should be a widget below which lets you listen to my recording and download it too so you can listen on your ipods when you're out running, and remember our time together, wrapped in each other's arms.

Hmm, the recording kind of cuts out towards the end with a string grate, but doesn't it always end that way?


Enjoy.

Thursday, 11 September 2008

Database and server admin #3

Right, so I've started a new table for component properties, where at the moment there are three columns, PARTNO, TYPE, PACKAGESIZE.

Where the PARTNO exactly maps the parts list, one to one. The part TYPE falls into three categories, S, T, and M as shown in the Venn diagram below.

(the just joking subgroups being things like solder blobs instead of zero ohm links and bare wire links respectively - they aren't discrete components)

PACKAGESIZE is kind of more complicated, and I'm not sure what the best way to do it is. For surface mount, everything comes on reels or tubes, in fact some parts can come in reels and tubes. Aw man, sometimes even comes in cut tape or individual bags. It varies. So its a description of the part, 0805, 1206 for caps and resistors, and then SOIC8 or SOT-23 and so on for the ICs. For through-hole parts sometimes they come bagged, sometimes in ammo boxes, and sometimes in reels. But again, its inconsistent, so instead PACKAGESIZE is left blank, I dunno, maybe it could be if the part is axial or radial, but I dunno if that's any use to anyone.

For reeled and cut tape surface mount parts, when we kit them, we need to give about 200mm extra everytime, regardless of kitting quantity. For bagged through hole parts 2% extra should be okay, but what about reeled through hole?

And should I build this into my kitting SQL query? That depending on package size, you need a different quantity?

I know nothing about forms, so every time I want a kitting report, I build it from scratch, going 10% over the required quantity.

Needs to be a bit more sophisticated.

And linked tables, I don't even know if they're possible in OpenOffice Base, but it would makemy life so much easier and save having to spend time every day cross-checking different tables. But every time I search google, there's nothing of any use. Bah.

Tuesday, 9 September 2008

Database admin

I want to hire me a database consultant, I can afford about £20 a week, and whether thats for two hours or one hour, I don't mind, just as long as this database monkey knows their stuff and can either tell me what to do, or do it themselves.

I have wee questions all the time, and I think it'll be money well spent.

This database monkey could be anywhere in the world, doesn't have to be local.

Example #1:-

I have one table, PART NUMBERS, with just two columns, part number and part description
Then I have another table, PART SUPPLIERS, with loads of columns, part number, supplier, supplier description, supplier part number, price, MOQ, manufacturer, manufacturer's part number, and many more.

Should I start another table for PART PROPERTIES, like whether they are surface mount, stored in tubes or reels, capacitance, tolerance, resistance, inductance, etc, which will all be fixed to the manufacturers part number (if I have it) or just our part number?
Or do I just add extra columns in the PART SUPPLIERS?
If I start adding extra columns, this could go on for miles.

Example #2:-
I have a table called BOMs which lists how many of each part is used in each subassembly. Some of the sub assemblies are mechanical, some are packaging and some are PCBs

I have another table called COMPONENT LOCATIONS, which lists where on each PCB each component is used.

I can run a neat SQL queries which counts how many of each component is used on each PCB, and then I manually update the BOMs table from this.

It was be really cool if it automatically updated the BOMs table, every time I changed a component on the locations table. I have no idea ho to do this.

And to complicate matters, it would be neat if each change was time-stamped.

Tuesday, 27 May 2008

Things and stuff

Wee a slightly unnatural yellow colour, and pain in my stomache where I think my liver or kidneys are. It could just all be in my head.

Got a mention on Sparky's Magic Piano's Facebook

One handed origami video that I haven't posted here before:-


Plimptons gig on the seventh, expect more spam closer to the time
6057813

The one thats shameless, shameless

Went to London Bloggers Meetup.

Arrived on my own without usual meetup companion Aref-Adib and as a result sat on my own in a corner for the first half hour or so, scribbling notes about the sponsored goodie bag.

Berocca make vitamin drinks and engery tablets, the goodie bag contain a fine branded glass (about 400ml), one branded biro, a tube of the Berocca effervescent tablets (orange flavour) and a pad of branded post-it notes.

I'm sceptical about the effervescent tablets, one side of the pack says "Taking more than your recommended daily intake may be harmful", the other side of the pack says each tablet contains 860% of your recommended daily allowance of some of the vitamins.

It is a fine branded glass they provide and the branded post-it notes make my mind boggle at the possibilities. But the warnings and information on the packaging are a bit ambiguous. Its like if a pack of razor blades says how sharp the blades are and then warns you against cutting yourself.

Anyhoo, Jaz and Lisa both recommended taking the stuff, so I have done. The fizzy bubbly goodness has burnt my lip, but I think the goodness will do as a surrogate breakfast for me tomorrow morning. It is orange flavour so I think it counts as one of my five daily portions (along with two cups of coffee, bacon roll and microwave meal).

I am well healthy me.

Met the following bloggers:-

Andy from the marketing blagger
Chris from qwghlm
Andy from Distributed Research
Jure from Zemanta
Lisa from u-handbag
Krista from Londelicious
Julius he's an event manager
Jaz from ShinyRed
Tom the Flashboy from The Metro

Also I think I glanced at:-

Carmen from Green is Not a Colour
Annie from the Going Underground
Peter Marshall from My London Diary

Back to work

Have not been to any gigs in a long time and Rosie's cutting down on her MSN time so I'm going to try to re-ignite the blogosphere with Last Night From Glasgow Indie Eyespy once more, but this time without it being such a money pit.

Need to persuade promotery friends and naive bands to put me on their guestlists and persuade hott single friends to accompany me.

Ooh, London Blogger meetup tonight, back at the Camel and Artichoke. Its almost like having a social life, but without people from Bowlie. I know, I know, its weird and un-natural, but they are nice people.

Thursday, 8 November 2007

FFS why can't SQL do heirarchy tree things easily?

I don't care if its a recursive join. I can easily write a three column list, (id, part number, parent) all my parts and all their depenancies and with duplicate lines, and I can easily, by hand, layout the damned heirarchy tree ting, so why does the entire internet database admin community not have an easy bit of cut and paste SQL code that'll do it for me.

I've got 3GHz of speed, 1Tb of hard disk memory and 2Gb of RAM, it shouldn't matter if there are five levels of recursion or a hundred, this damned 'puter should figure it out in a few seconds and display it.

Lazyweb, help me!!

Friday, 12 October 2007

Venn and Databases

Eeep, my two favouritest things in the world, Venn Diagrams and Databases, now, together at last.

Not sure if this is the same as setting up a relationship between my booking in table of serial numbers and my Warrant Card table of customer's products in Open Office. Are table "relationships" the same as "joins"?

In other new, after miss reading a post on the vegan thread I've found this mob who supply goat meat. Sounds interesting. They claim that 80% of the meat consumed in the world is goat, that can't be right, but I can't think of owt wrong with a nice goat steak.

Thursday, 11 October 2007

I'm really getting into this database business. At the moment I have ten different tables, and about four different part number lists, which I'll have to pull together later. I've mastered forms and sub-forms with wee nodules of SQL code, but I fear that generating reports could be a bit tricky.

I'm starting to suspect that using Open Office's database, whilst good for starting out into this new world, isn't going to cale well and I ought to be using MySQL. So can I export everything from OpenOffice to MySQL, and I mean everything, tables, forms and all, or will I only be able to export the tables and'll have to recreate all the relationships and forms?

At what point can I quit the database admin and management business and get back to manufacturing engineering?

Ooh, gigs tonight, with people and faces.