4

Concerning normalization of a one to many relationship, I am not sure how to represent a collection with a specified capacity in a database. I can think of two ways to represent an entity A with a one-to-many relationship with B:

  1. When Table A is created with n capacity, insert n rows into Table B. The amount of rows in Table B represents the capacity for that collection (Like allocating memory). These rows must be updated as elements are placed and removed from the collection.

  2. When Table A is created with n capacity, represent the capacity as a column in A. Rows must be inserted and deleted from Table B as elements are placed and removed from the collection. The application ensures that table B has no more than n rows.

What are the benefits or detriments of each of these ways? Is there a standard method for doing this? Are there performance or maintenance concerns?

  • I don't get it - in #1, how is the current number k of elements in the collection represented? If the collection contains k elements, there must be k of the n rows in table B which are "used" or "valid", and the other n-k are unused. Do you have something like a boolean "valid" flag in mind for table B? – Doc Brown Dec 22 '15 at 22:07
  • 1
    Surely the answer is obvious when you consider an instance of A with 1 million capacity and 0 Bs – Ewan Dec 23 '15 at 0:06
  • @Ewan, yeah for that case it makes sense. For some reason my brain was thinking add entries before hand, as I related to allocating memory. However, it makes more sense with your example to imagine it like nodes of linked list in a way - adding the entries as necessary. – AaronCarson Dec 23 '15 at 3:07
  • @DocBrown, The example for #1 would likely contain more information such as a discriminator and reference to another table. For instance, an entry such as an OrderItem for a Purchase Order, where each OrderItem has a Product and a Quantity. In that case the fields would be null if the row is available but not used. – AaronCarson Dec 23 '15 at 3:11
  • So you see where #1 leads you - an overly complicated construct which is hard to maintain and wastes memory. And for what purpose? A database is not like classic memory where it might have benefits to allocate memory in chunks. – Doc Brown Dec 23 '15 at 6:22
2

What are the benefits or detriments of each of these ways?

Option 1 Benefits

  • Can be enforced at the database level by granting users update privileges on B but no insert or delete privilege. Obviously a privileged user (maybe a stored procedure) should create the initial rows.
  • The capacity, being an attribute of A is a column of A, which makes sense.

Option 1 disadvantages

  • None that I know of.

Option 2 Benefits

  • The capacity, being an attribute of A is a column of A, which makes sense.

Option 2 disadvantages

  • The application (or applications) must enforce a business rule that could be easily enforced at the database level.

Recommendation:

A combination of both:

  • Store the capacity of A as a column of A.
  • A privilege user (the backend) will create as many rows in B as A's capacity.
  • Normal users can't insert or delete from B, just update.
  • When a change is made to A's capacity, the backend inserts or delete rows so the number of rows match A's capacity.

Is there a standard method for doing this?

Frankly, I don't think so.

Are there performance or maintenance concerns?

Option 2 can create inconsistencies due to programming errors.

Note:

There's no such thing as "the application". The one-app<->one-DB rule doesn't apply nowadays when you will potentially have several front-ends to the same backend.

Possibility of adding a trigger to enforce option 2:

In don't know what your RDBMS is but this is a pseudo code:

create trigger mytrigger before insert on table B for each row
declare
  var c integer;
  var ca integer;
begin
     select count(*) into c from B where id_a = :new.id_a;
     select capacity into ca from A where id = :new.id_a;
     if c > ca then
          RAISE_APPLICATION_ERROR(-20001, 'Cannot Insert mora than '||ca||' rows in this table');
     enf if;
end
  • I'm not sure the disadvantage you talk about for Option 2 actually exists. You can easily enforce this at the database level with a trigger. – Vincent Savard Dec 22 '15 at 21:00
  • @VincentSavard OP indicated that option 2 involves the app doing the validation, he/she explicitily stated that as part of option 2, whereas in option 1 the physical rows in B enforce the capacity rule, I only added privilege restrictions once rows are created. – Tulains Córdova Dec 22 '15 at 21:04
  • I see. It's unfortunate because Option 2 would be a much better idea if the rule was enforced with a trigger instead of the application. – Vincent Savard Dec 22 '15 at 21:06
  • Good note on the possible one-to-many relationship of a database and an application, even if in my context there it will be one-to-one. I like the database-level consistency enforced here. I feel the hybrid solution is best if the application is enterprise level and has multiple users. Otherwise, option 2 seems easier. – AaronCarson Dec 22 '15 at 21:07
  • Can someone explain how to enforce the rule with a trigger as a separate answer? That would possibly be another good solution. – AaronCarson Dec 22 '15 at 21:08
3

For at least two reasons, option #2 is the winner to me (although not sure if this is considered "standard" reasoning):

  1. With option 1, you will have wasted/empty/null rows that are added by default every time an instance of the parent entity is created. Not only does this potentially waste resources, but you'll probably have frequent times where query results have to be pruned to account for this, and could make any stored procedure code uglier.

  2. Logically, it's not really the DB's responsibility to enforce the capacity constraint. As long as the parent entity records the capacity, your model is complete. It's then up to the app, as you note, to ensure that the model's requirements are enforced.

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.