On that note, one issue I’m pondering is how strict I should make the data entry scheme for the typewriter database. for instance, here’s a possible schema for that table:
CREATE TABLE typ_typewriters (
typ_id int(11) NOT NULL auto_increment,
last_ts timestamp NOT NULL default CURRENT_TIMESTAMP,
member_id int(11) NOT NULL default ‘0’,
manufacturer varchar(250) default NULL,
model varchar(250) NOT NULL default ”,
serial_no varchar(250) NOT NULL default ”,
typ_year int(4) NOT NULL default ‘0’,
typ_thumb_img varchar(100) default NULL,
type_sample_img varchar(100) default NULL,
typ_desc text,
typ_active tinyint(3) NOT NULL default ‘1’,
PRIMARY KEY (typ_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
where ‘member_id’ is a foreign key for the user table, and ‘manufacturer’ and ‘model’ are simple text fields. What I’m wondering is if it would be a better idea to enforce choosing from hardcoded selectors for those two fields rather than allowing freeform entry. I suppose it depends on how important it is that all Smith-Coronas are listed as “Smith-Corona” rather than “Smith Corona” or “SCM” or some other variant. If you wonder why it takes so long for IT projects to start up, it’s because us coder types tend to overthink these things.
So you have been bidding on all my TRS-80 Model 100s recently:)
actually, no. The only thing I’d ebay for would be one of the barcode reader wands, as I lost mine many years ago. Sadly, they’re rarer than hen’s teeth.
In a perfect world, serial number would be the PK, but some people might not know how to find them and thus we’d miss out on logging a typewriter. Could keyboard layout be deduced from a serial number? If not, having that entered in the desc text would work. You should probably want to give some suggestions on what description you want, basically color, keyboard layout, etc.
From a data entry perspective, having another table that IDs all the manufacturers so the user is just selecting from drop-down will give you consistency. If you are confident enough, you could even break that out into model names. In both cases, you can always give an “other” option and allow free-form.
Maybe later, you could do a check on the known serial numbers and give the user, upon data entry of the serial, the date your source has.
I’ve got extensive experience with relational database design, so would certainly offer my time to brainstorm on the table designs…
Well, I’m going from the assumption that we have zero existing serial# info to begin with, as I’d be hesitant to scrape tw-db, and even if it was ok to do so, I’m not real sure exactly how the serial number ranges work (are the serial numbers shown the bottom end range for the given year, or the top-end, or somewhere in-between?)
I’m not real confident about the Models, as there are some inconsistencies (example: the whole Remie Scout line of the 1930’s). Maybe a freeform entry with Google-like ajaxy ‘suggestions’ to at least prod the end user to be consistent.
One thing I am stuck on is the concept of linking machines from different manufacturers that are ‘design-alike’ (Empire Aristocrat/Viking/Hermes Rocket, etc.) – a simple “identical to:” textfield would work reasonably well, i suppose, but I’m hoping to work out a better idea.
A drop-down is fine if there’s an easy way of adding a new entry to that table when you get something unusual. I just keep a spreadsheet currently but will probably convert it to something more useful. The spreadsheet autofills with existing entries but I can add something new too. In mine I make the distinction between “brand” and “manufacturer” as in the underwood made by remington, or the olympia made by nakajima, as well as the store-badged machines. Not to mention that I have a Corona 3 with no Smith. Also I have a couple on which I have yet to find the sn – one bulgarian and the other I just can’t find it amongst all the works. It would be neat to have a standard and be able to pool our collection data for patterns.
Good point about the distinction between “brand” and “manufacturer”. I’ll play around with various schema and keep posting them for suggestions.
I’m beginning to wonder if I should make a distinction between machines that people actually have and “reference” entries that could be gleaned from existing references just to flesh out the serial#/year data. Hmmn.
Couldn’t hurt to throw a “brand” column in there (as in, a Smith-Corona made model, branded as Sears) Serial number would certainly be a lousy PK, since they vary in length, format, and uniqueness, even within a brand. Consider Olivetti “reset” the serial numbers on some models, so newer machines have lower numbers than later ones.
“cpi” (characters per inch) would be a reasonable thing to store, as int(3) perhaps — there are some seriously tiny typefaces out there that may approach 16 cpi. Or make the int() unsigned, if you can — unless you want to use negative cpi to indicate a right-to-left machine (Hebrew, for instance.) While we’re at it, a typeface code number would not be out of place — then you could cross-match all machines by the same maker with the same typeface.