0

Thanks in advance for your answer. I am trying to tune my db skills.

I have 3 tables:

Brand, Model and Item.

All models belongs to a brand, but an item can have a brand but not a model.

I have 2 questions:

  1. From a programming point of view I just add brandId as foreign key and ModelId just as a field to Item table.But from database design perspective, I guess this is the wrong approach. How I should manage it?

  2. In the case that an Item always have a model and brand. I usually only add modelId and from it I can get brandId, but I have been seeing a growing wave where both fields would be added to item table, again, what is the correct approach?

1

Your question hits a 'gray area'; there is not an overwhelmingly 'right' way to design the schema.

When the question is asked about city + state + country, I say: Have one Locations table that contains all 3 columns. And use country_code CHAR(2) CHARACTER SET ascii.

I also say "normalize, but don't over-normalize".

There are 'religious wars' fought over your question. There are valid points on either side. Flip a coin, implement it, then learn from the decision.

Better yet, implement both ways - writing the CREATE TABLEs, the INSERTs, the SELECTs, etc.

Still better is to implement both, but have a "database abstraction layer" in your code that hides the choice from your application code. Coming up with a single abstraction for your app that works with either backend will be the best lesson here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.