PRimary key & Performance dilemma!!
-
Hello,
I have a problem here. I have started with a new company and they are pretty set in their ways of doing things and changing anything requires more than just,, well you know..
My problem. I have to design a database, basically a customermodel database, let me explain;
Each User (User of system) have customers, and for each customer they have models. Ok before we go any further, the Only customer details we ever need to keep would be the Customername, thats it.
Then In the models, a customer is not allowed to have the same modelname twice. Modelname is varchar(75).
To maintain data integrity and performance, what would be the best way to do it?
My Solution 1:
Have a User table with user details and a customer link table that link customermodels to users, and then use a trigger on the models database to maintain modelname integrity.
My Solution 2:
Have a user table with user details and a table with UserID, CustomerModelName,.. and use those 2 fields to form a Composite Primary key on the table.
What is the best way? or is there a better way perhaps?
Any help would be appreciated, and any links to resources and documentation as to why that is better, even more!!
Thank you,
Ryk
-
Personally I would have three tables.
- Customers
- Models
- CustomersToModels
If you haven't already done so I would create an integer field to act as the primary key for the Customers and Models table.
On the CustomersToModels I would have two fields.
- CustomerId
- ModelId
I would put a primary key across both fields but I wouldn't cluster it because there is no guarantee of the order of the records.
I would also add an index on the ModelId field.
Don't add an index on the CustomerId field because the primary key will cater for searches on CustomerId in isolation and CustomerId & ModelId in conjunction.
If there is a record in the CustomersToModels table then you know that the customer has already been assigned a model.
-
Interesting solution, although you might have to modify it, slightly. From my reading, I understood that a customer cannot have duplicate modelnames but could several customers have the same modelname? Is that true?
Quand on parle du loup, on en voit la queue
-
Thank you for your replies,
Patric, that is correct, only 1 customer is not allowed to have the same two modelnames, but multiple customers can have the same modelname.
I am still uncertain as to what to do, still trying to read up on it.
Thank you,
Ryk
-
David's approach is by my understanding the more conventional way to maintain a many-to-many relationship. It's main advantage over your two solutions is that if you re-use model names a lot or users often have many different model names then it conserves space. This space savings is best observed as more tightly packed indexes allowing less RAM to be walked and cached. You likely wouldn't care about disk space for this. I think it may also play nicer with data modeling tools, though I've never had to actually use one.
It also gets rid of a trigger from your Solution 1 which, although not necessarily a bad thing, adds unnessary hidden complexity. It is generally easier for someone to come by later on and understand the relationships and constraints in David's approach than it would be for them to A) notice you created a trigger at all and B) understand what the trigger is accomplishing. That is just a personal opinion.
-
Well it is not really a many to many relationship, it is more 1 Customer to Many models. Under Models each customer can have multiple models, but the same customer is not allowed to have the same name twice. The same name can be used many times in the table, for different customers.
Is there any software out there that I can use to test these different scenarios and see which performs best?
Thank you for your help so far, it is getting a bit clearer.
Ryk
-
The central question seems to me to be:
Can different customers share 1 model, or is it a standard 1-many so each customer can have many models but each model has only 1 customer?
If the latter, I think you have a simple structure of 3 tables (user-<customer-<model). The restriction that each model name can only be used once per customer is a business rule, not a feature of the data model. Hence you might argue that it should be implemented in your update procedure or method.
-
The three table solution is the correct one. To keep the data integrity, you simply create a unique index on the combination of customer id and model id.
And, it is a many-to-many. A customer can have many models a model can be related to many customers. What about that is NOT many to many?????
-
Oh, one more thing, I would structure the intersection table like this
tblCustomerModel
CustomerModelID (PK)
ModelID (index, AK1)
CustomerID (index, AK1)
PK - Primary Key
AK9 - Alternate key + the number of the index
I am onr of the "nuts" that does not tolerate combined keys and totally believes that primary keys should be meaningless.
-
AK1 needs to be a unique key if you are going to do this, otherwise you will take an AK47 to your data integrity.
As this table simply maintains the relationship between the customer and the model I wouldn't bother putting a CustomerModelID primary key.
If there was additional data to be associated with the customer/model association then I would put in the CustomerModelId field.
Combined primary keys are OK provided that they cannot be changed. Changing primary keys is a big no-no, particularly if the primary key is clustered.
-
Alternate Key means just that, and alternate unique key. Don't use ERWin or other modleing tools do you>
-
I've heard of modelling tools, but then I've heard of unicorns and phoenixes as well
To me Key = Index.
-
Thank you all for the help. The company is very sensitive to making changes so what I am doing now is to wite a quick c# app that will gnerate test data in both structures, and then measure the time and performance it uses to insert a single row and select a single and multiple rows.
Just one correctionon what Robert said "And, it is a many-to-many. A customer can have many models a model can be related to many customers. What about that is NOT many to many?????"
No it in not many to many. One customer can have many models, but one model will only ever point to one customer.
Thank you very much,
Ryk
-
"only 1 customer is not allowed to have the same two modelnames, but multiple customers can have the same modelname"
Based on your statement earlier, it is a many to many.
Based on your new statement it is a mon to many and the structure I gave you is now incorrect.
-
Do models have any attributes other than name? Are identically-named models for different customers really the same thing? I can't tell from the requirements stated so far.
If one customer is a hardware store and another is a massage parlor, the "Screw" item is not the same thing.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply