General DB design question

[DSect]

Hello.
I have inherited a database with some very questionable design techniques. The one that is particularly troubling is the use of a composite code to track the status of a person in the database.

Scenario:
We run a member-based place. Each member has a status code and that code tells quite a bit about each member. The code is 6 digits long and can be a combo of letters and numbers.
A brief explanation of some of the code definitions in use is:
First place digit: Member Status – Values: 1, 3, 4, 7
Second place digit: Publication Status – Values: S, A, K, B
…and so on...

So you can have a code like: 1SX02E with each place meaning something different. Now I know this is stupid design, but I am having a hard time explaining it to my co-workers. I tried to tell them that, but I am having a hard time explaining why it’s stupid, or what a good alternative is.

Right now, our queries look like this: SELECT … WHERE .. LIKE ‘???4??’ (< Access 97 language) and they have been able to extrapolate whatever info they needed by doing a bunch of stuff like that, but it is a real pain in the butt to make these crazy queries and stuff...

I am thinking that since the code only has so many possible combinations that we can write out a list of numbers with a unique number representing each possible code combination.. Like this:
Status: 1SX02E = Numeric Value 1200
Status: 1SX02F = Numeric Value 1201
…and so on…

I’m very confused as to where to take this. I am also thinking about taking the composite code that we have now and making a database column for each digit..

HELP!!

Please post if you need more info.. Basically.. I am just looking for an angle of attack and not a real detailed solution.. Thanks as always!
-Chris-

 

[WildHare]

The code is 6 digits long and can be a combo of letters and numbers.

Is EVERY members code 6 characters long? If so, then it sounds like you might really want a 6-field composite key of single character values..

Could there be dupes?

If you atomize this field down to it's smallest component part, you'll at least be on the path to correctness. You can always 'fake out' the user by showing them a text box and parsing the single characters you want out of it.

Then you can query on the specific field without having to use LIKE ????4?...

Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[DSect]

Hehe..

I was thinking off track. You said to atomize the fields and now I think that is most definately the easiest and quickest way to bring this under control.

I guess it all comes down to normalization and the composite field is the exact opposite of that. I can concatenate the display for useability, but the storage will be as 6 seperate fields.

I still don't know why I was thinking about making a number to represent each possible combo? Maybe because the digits are used in various combos to yield info about the member. Heh.. That's easy (maybe easier) to do with stipped-out fields.


Thanks!

-CC-

 

[WildHare]

That's easy (maybe easier) to do with st[r]ipped-out fields.

Exactly. Each of the six individual characters MEANS something. Member status, Publication Status, what-have-you.

As long as their occurance is unique within the table, I see no problem making a 6-field composite key from them. Your problem may come about if anyone has only 5 guys, or 4 guys...you can't have a null key field, so we may need to do further study... Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[shmiller]

Either I'm a little confused or WildHare is.

You're not using that code as a unique identifier for members are you? If not, I would definitely split them into separate fields. They each have separate meanings and should never have been put in the same field. That's like putting someone's address, occupation, and phone number in the same field.

If that code was being used as a unique identifier, you still need to split it into separate fields, then you need to use something else as the primary key. Create a separate member number or something.

 

[WildHare]

Ehhh..we probably both are. After reviewing this post, I don't think using the 6-character guy as a 6-field composite key is very worthwhile - like you say. I'd probably figure some other, better, key field.

I guess my point was the treatment of the 6 possible values as individual values, not as a 6-character string.

Good point, though.

Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm