I need to be able to have a user be provided with the next available filename for their project based on the following rules.
3 characters to represent a company name or location
1 character that denotes a model number (i.e. 2 for the 3200 series then 3 for the 3300 series)
1 character for the validation type which is a number 2 or a 3 only
1 character for telling of its production or development
1 character for the version number. ( it was suggested that it be 0 - z ) don't know how else to do that with one character.
If anyone has a better idea for file naming denoting all those things in only 7 characters then let me know. Files get loaded onto an electronic device and it will not accept file names longer than 7 characters.
I figured I would create an interface so that someone could click on the location and click a check box for prod or dev and so on and it would spit out a viable filename for the user to key in when the application prompts the user for a file name. There may be a great many of these files so I am facing quite a challenge to make sure they stay in order, we can spot details of a file based on it's name and making sure they are unique.
Any ideas?
Something like this should be good enough to start testing. It won't work in MySQL as written, but you can replace the check constraints with foreign key constraints.
create table filenames (
company_name_or_location char(3) not null,
model char(1) not null,
validation_type char(1) not null check (validation_type in ('2', '3')),
prod_or_dev char(1) not null check (prod_or_dev in ('p', 'd')),
version char(1) not null,
primary key (company_name_or_location, model, validation_type, prod_or_dev, version)
);
Notes
All columns allow any alphanumeric character, including (probably) Unicode characters. That might not be what you want. The best way to eliminate Unicode characters is probably by using a collation that doesn't include Unicode. You probably want to do that for the table, not for the whole database.
If you use a surrogate key, you still need all the other constraints I wrote for that table, including a UNIQUE constraint on the columns {company_name_or_location, model, validation_type, prod_or_dev, version}. (You need all those constraints, or equivalent constraints that account for Unicode, for whether model should allow numeric or alphanumeric, etc.)