Custom keys

[Edgewalker81]

Is a way to make custom primary keys based on auto-increment?

Now I insert a row, then use mysql_insert_id() to get the number, alter it (adding a prefix such as "E-") and save it in another field.

 

Is there a way to simplify this?

 

I want the prefixes is so I can know what kind of object it is, and so which table it is in.  I can't just make one table because the items have no data fields in common.

 

I thought of just making a table with an autoincrement field and a field for which table the item belongs to, but I'm worried that if the number of items grows large, it would be quicker to have my PHP check the prefix and just do one query instead.

 

Any advice appreciated.

[mb81]

My question is why are you looking at primary keys from different tables at the same time if it is a different set of data fields? If this is from an sql query, then just set an additional field for each table, example:

 

SELECT 'E' AS persontype, name, address FROM TABLE_employees UNION SELECT 'C',name,address FROM TABLE_clients

[mb81]

My question is why are you looking at primary keys from different tables at the same time if it is a different set of data fields? If this is from an sql query, then just set an additional field for each table, example:

 

SELECT 'E' AS persontype, name, address FROM TABLE_employees UNION SELECT 'C',name,address FROM TABLE_clients

 

Then, you just do a check on what the "persontype" field is, if it's E, you handle it one way, if it's C, you handle it another.

[mb81]

Sorry about that, meant to modify the original post, not post as a reply.

[Edgewalker81]

I just knew you'd ask 

I'd rather not, to be honest, but the project is for barcoding a large number of items, and I need to be able to answer the question "what is this thing?" It might be an order form, a service request, a CD, a bag of photos, or many other things.

 

They are all scanned into one field by the user, so the system has to be able to figure out what the item is so it can display the info relevant to that item type upon being scanned.

 

I'm thinking an table that indexes what each item is might be in order...

[mb81]

If I was starting from scratch, I would have created one table for all the different items, and a category for each item, then in your php code, you can say that if it's in the order form category, then you need a field or order number, or total amount, or whatever your fields are, where as cd might have a title and description.

 

The other option would be to do the index file as you said, but I wouldn't create a seperate index, just use the indexes that already exist, but also include the tablename, so your data looks like this

 

[pre]

barcode        tablename        tableid

0000000001    cds              5

0000000002    orderforms      3

0000000003    servicerequest  4

[/pre]

[Edgewalker81]

Yeah, I was thinking something like that.

Luckily this system is pretty much being redesigned from scratch, so I can change the way it's set up.

 

Thanks for the help.