0

I have these three tables on my database: enter image description here

There is a one-to-one relationship between Code and Aggregation table (an AggregationId is always a CodeId. And of course, a CodeId is unique). Note: A Code.CodeId doesn't need to be in Aggregation table.

There is a one-to-many relationship between Aggregation and AggregationChildren table (an aggregation will have one or more children).

I think I'm wasting a lot of space repeating a NVARCHAR(20) data in three tables Code.CodeId, Aggregation.AggregationId and AggregationChildren.AggregationId.

By the way, `AggregationChildren.

What do you think?

I think I can delete Aggregation and AggregationChildren tables.Then, add two new columns to Code table: One to indicate to replace Aggregation.AggregationId. Another one to replace AggregationChildren.Position.

Improve this question
1
  • You need to think more about this design before implementing. I recommend using auto incrementing integer columns for the primary key in all tables for performance reasons. Use these integer columns as foreign keys in the other tables that require it. By eliminating the Aggregation and Aggregation Children tables as you suggest, you are simply making one big table which is a separate headache altogether.
    – datagod
    May 13, 2016 at 13:11

2 Answers 2

Reset to default
1

If the multiple use of NVARCHAR(20) bothers you, change the type to INT and adapt the Code table to have two columns: ID (INT) and Name (NVARCHAR(20)).

Improve this answer
5
  • No, because CodeId must be unique, and if I add an ID column I will have two index.
    – VansFannel
    May 13, 2016 at 10:36
  • 2
    2 indices - who cares. You gain a lot of speed, comparing strings is SLOW. And uniqueness can still be enforced.
    – TomTom
    May 13, 2016 at 10:40
  • I'm not asking this.
    – VansFannel
    May 13, 2016 at 11:30
  • And I'm not going to work with new ID column, I will use CodeId column to search and avoid duplicates. It has not seen to add a new column that it's not going to be use. Sorry, but your answer doesn't answer my question.
    – VansFannel
    May 13, 2016 at 12:15
  • If CodeID must be unique and you collapse your design into one table, how to you think that is going to work? You will have one aggregation per code and one aggregation child per aggregation.
    – datagod
    May 13, 2016 at 13:13
1

From your definition the relationship is 1 to (0,1). This is different than a strict 1 to 1 relationship, and is normally modeled as you have done. Your model is the one I would use.

If aggregation child can only belong to one aggregation, you will want a unique id on that column.

CodeId is the natural key, however this does not necessarily make it a good primary key. You may want to consider using a surrogate key for a couple of reasons:

  • If CodeId could ever change you will not need to cascade the change across multiple tables.
  • Numeric surrogate keys tend to perform better as foreign keys. However, a list of aggregations or aggregation children would require joining one or more times to the code table.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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