Hi there

I have a DB design question, is composite key better or combining fields to get a primary key better?

I believe combining fields to form a unique primary key is better but I want to hear the advice from those who have more experience in DB design.


Another question,

I have a client table and a client table could have joint many services and each service has its own table.

When I find what services have the client joint, I don't want to search all service tables to get the answer. I have worked out a solution and want some advices. My solution is that, for example there are service A, B, C, D and a client has joint services B and D. There would be a field in the client table that contains a string "BD". What do you guys think? Has anyone faced similar problem and has a better solution?


Thanks for any advice!


Kind regards

Edmond

I'd say, apart from DB design issues, go with a single field key. Hibernate likes them much better. :)

can you describe what is and how you're using combining fields?
it seems very interesting (my dba never talked me about this)


Thanks guys

Regarding the second question: it's usually better not to encode relations using special ad hoc strings. Typically this kind of approach breaks down when you get more complicated examples.

Instead, I would define a ServiceDefinition table, with one row for each service and a many-to-many relation with Client.

is combining key = "id1" + "-" + "id2" --> in string?

if it is, it could be dangerous for performance

Yes.

the PK would be: "id1" + "id2"
but id1 and id2 still have their own column in the table.

Also the search criteria is usually "id1" + "id2", so why it is dangerous for performance?

He might be referring to string concatenation, which when done too much is inefficient in Java. If you want to concatenate strings, especially inside a loop, use StringBuffer.

manipulating string is always more expensive than "NUMBER",
but you might be right about the hibernate size, i don't know which is the best for performance a string unique key or a composite with equals/hashcode...

Yes, I meant concatenating strings.

Let me explain the problem more.

I want to uniquely identify my client with an ID and I have choosen to use their driving license no., passport no. or identification no. as the primary key. Since, the number on the driving license and passport could be overlapped, therefore, I need to uniquely identify a client with ID_Type + ID_NO fields.

I don't want to use composite key in my table, so I concatenate the ID_Type and ID_No fields to form a PK.


In my application, users usually give me the both ID_Type and ID_No to retrieve its account information.


Thanks

Don't do that. :)

Generate your PK from a sequence. You can use those other fields to perform searches on your data, even have unique indices on them, but don't use them as the primary key. It isn't good database design for several reasons. (Google for "surrogate keys") The main reason is that a PK should have absolutely no utility other than uniquely identifying a record, and should never change. The other data values have "business" meaning, and thus break that rule.

but what about integrity

example
table order, pk = no_order
table order_line, pk1 = no_order (+ fk on order) , pk2 = no_line

if you have a unique sequence pk

you can have two records like this:
1- pk = 10002, no_order = 10, no_line = 1
2- pk = 10003, no_order = 10, no_line = 1
which is not integrity???

Just because you have a surrogate key as your PK doesn't mean you can't place a uniqe constraint on other fields.

ok so the best way is:
- primary key = sequence (only one field)
- + unique constraint on our 2 fields

cool,
thanks, i'm not an expert in db, i'm just curious and if i can propose this solution to the db team, to avoid composite-id... that's great


Anthony

Yes, that's absolutely the way to go.

Here's a pretty good surrogate vs business key article: http://www.bcarter.com/intsurr1.htm

thanks a lot,
it would great to explain this somewhere on a wiki which deals with composite id...