1

Me and a coworker are seeming to disagree on this topic.

The context is Product that have multiple components. Sometimes its multiple of the same component.

I think its better to have a record for each component where as my coworker thinks its better to have a quantity column. Personally I think this is what a one to many relationship in db design represents in a very real since, where he is saying its easier to work with (he is more of a c# guy where I am the dba). So, I can def see where we might be coming at this form different angles.

I guess since I am new to this, I figured I would ask the people who know better what their opinions are.

Which is better - extra rows per "product" or having a quantity column?

|improve this question
  • Are you talking about a bill of materials? – McNets Jul 29 at 14:27
  • No - just a table used for detailing the product components. – Doug Coats Jul 29 at 14:28
  • 1
    I think this question will be closed as "Primarily opinion-based", but IMHO your co-worker is right. – McNets Jul 29 at 14:39
  • Well I was hoping for reasons and points to be accompanying the answer. Like if Im wrong I would like to know why. – Doug Coats Jul 29 at 14:41
  • I too think your CoWorker is right. That's not what a one-to-many relationship is about. – a_horse_with_no_name Jul 30 at 11:34
3

Your table structure is defined by the entity model it represents, not by opinions.

If components need to be represented individually, e.g. if they have unique serial numbers, different prices, or arrive as separate shipments, this would warrant separate records for each. If components are indistinguishable from each other (cf. nails, bricks, or gallons of paint) there is no reason nor method to track each one separately.

|improve this answer
0

Depends what you are trying to use it for. If there are duplicates, I'd rather have a quantity table along with it so that you could use it in WHERE statements without having to first use COUNT() aggregate. It would also be smaller amounts to store for INT datatype than several of the same GUIDs. 

SELECT * from table 
WHERE GUID ='123456789abcdef' AND Quantity >= 5
|improve this answer

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