Both a Peon AND a King

[Banaticus]

Up till now, all customers have been a Peon or a King. One of the cells in the customer information table is "Type of Person" -- it's a lookup to the "Rank" table where all the different ranks are listed, Peon, Merchant, Duke, King, etc. This makes the "Type of Person" field in the Customer Information field a drop down list where a rank can be chosen.

But now, I've been told that a given customer (actually, more than one) is both a Peon AND a King. I need to change this field from being a single value, chosen from a list (viewed through the lookup wizard -- A or B), to a sort of multiple selection (A and/or B).

Any advice?

 

[Pat Hartman]

This is one of the most distressing after the fact discoveries. Your application is already designed, everything is working, and all of a sudden, you have a new 1-many relationship. First comes denial, then when reality sets in, you want a quick and dirty solution which you think is to store multiple values in the same field. Well, sorry to rain on your parade but the best solution is to fix the structure now before you do any more development.

Once you have more than one of something, you have many and many requires a second table. You'll find MANY posts on this topic and the advice is always the same - make a new table.

 

[Kodo]

to expand on what Pat is saying:

You'll need a bridge entity.
table1: People
ID
person name
...

table2: Type
ID
type name
...

table3: Person_Type(this is the bridge)
PersonID
TypeID

this will allow you to have unlimited people with unlimited types and many people of different types all without repeating the type name in one table. You would link the person table to the Person_Type table on the Person.ID=Person_Type.PersonID and the Type.ID=Person_Type.TypeID

Make sense?

 

[Banaticus]

Thanks.

I had a person table and a "person type" table. The person table had a field that would lookup a single chosen value from the person type table. I've now created a new table "desired type" with a person number and a field that looksup a single chosen value from the person type table. I've copy/pasted the person number field and desired person type field to the "desired type" table.

I've created a reference with referential integrity between the "person" table and the "desired type" table that will cascade updates and deletes. This way, if a person is deleted from the "person" table, the associated records will also be deleted from the "desired type" table.

Thanks.