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?

  • 5
    You should have a table per entity type as a rule. 180,000 records is NOT very much at all, so I'm not sure how this is an issue. – JNK Jan 7 '15 at 18:02
  • Ok, thanks, that makes it easy then. I think I'm just too used to visualizing tables in excel. – Evan Jan 7 '15 at 18:06
  • 4
    In sql server, 180k records is really a very small data set, especially if you're only talking about a handful of columns. Most medium-sized databases will have transactional tables with 10s of millions of records, and if you do data analysis/ETL you deal with records in the billions. – JNK Jan 7 '15 at 18:08
  • Agree, this is not that large. And the complications of dealing with 12,001 tables is a considerable impact on the complexity of your code. But just a few tables and filtering for the rows related to your current task is pretty straight-forward. – RLF Jan 7 '15 at 18:10
  • @Joe - Perhaps you could propose a new question. Or look for a previous discussion of the question. It can get pretty passionate. – RLF Jan 7 '15 at 18:31
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.

  • 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 '15 at 19:48

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.