2

I just had a question about database design, I am in fairly early learning stages of this so bear with me.

Suppose I have a parent table called "Projects" each "Project" Entity can have any number of "Attachments" and each "Attachment" can have any number of "Anchors".

I can only see a few possible solutions for this. Estimations assume 10 years use of this database: 300 Projects/year * 3 Attachments/Project * 20 Anchors/Attachment

Solution 1 (My initial design idea):

Involves 3 tables. "Projects"->"Attachments"->"Anchors"

Number of Tables: 3

Max entities in a table: 180,000 in "Anchors"

Solution 2:

Involves 2+n tables. "Projects"->"Attachments"->"Anchors[n]"

Number of Tables: 9,002 (9,000 Anchor Tables)

Max entities in a table: 9,000 (In Attachment Tables)

Solution 3:

Involves 1+n1+n2 tables. "Projects"->"Attachments[n1]"->"Anchors[n2]"

Number of Tables: 12,001 (3,000 Attachment + 9,000 Anchor)

Max entities in a table: 3,000 (In Project Tables)

To summarize:

What's worse, more entities or more tables? AND/OR Is there simply a better design for this situation that I am unaware of?

0

2 Answers 2

2

The number of entities is driven by the functional specs on the database. You discover entities by analyzing the subject matter. This is different from inventing entities because it feels good. You determine whether any given subject matter entity is relevant to the database by looking at the scope of the database, per the requirements.

The number of tables is driven by the database design. You create tables as needed to fulfill the design goals. You are typically pursuing multiple goals: normalization, simplicity, flexibility, and speed, among others. Typically each entity will have a table of its own, and there will be extra tables for relationships that are not simple enough to just add FKs to existing tables.

The difference between analysis and design is crucial to all projects beyond simple exercises.

0

I was taught by a really good teacher in relation to this. One table per entity for 1NF. You may need to create more tables in 2NF and 3NF but its the best way to start it out. Always... A well designed database will mean less maintenance and more accuracy in the long run.

The best way to do it at first is to write it down on paper. Define your entities, then your attributes. Do your ER diagram. Define the relationships. Construct the tables and then do the normalisation process. i have designed databases for sensitive information which are critical. One thing here seems to be that you have started your database design with code. Draw it out. Please.

1
  • I think you may be confused by my array notation for n tables. That is simply to get the idea across, I hadn't touched code at the time of writing this. I started by thinking of and listing objects in MSWord, then translated that to necessary table definitions in excel. It was at this point that I began to question whether or not my tables would get too large and if there might be an alternative design. In my initial design I was already unknowingly applying normalization, because it just seems like common sense to me. I have since researched the topic.
    – Evan
    Jan 14, 2015 at 19:48

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.