CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
user765368
user765368

Reputation: 20356

data representation in mysql tables

Let's say I have three tables in a mysql database that contain some data like this:

- Sections (which have a number, a title)
- Sub-sections (which also have a number and a title but the title is the same as the title of the next data type with is 'statements')
- Statements (which have a number, a title (the same as the title from Sub-sections)

As you can see above, the field title from both tables subsections and statements are the same and they have the same data. Is there a better way that I can represent this in my tables so that I won't have that duplication of data?

Thanks for any advice

Upvotes: 1

Views: 395

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52147

Any redundancy can lead to data corruption: if there are two pieces of data that represent the same information, and one of them changes, you suddenly don't know which one is correct. Effectively, you have lost the information!

That's why normalization is important: it minimizes redundancy (and dependency).

Sometimes, redundancies are made on purpose for performance reasons (so called "denormalization"), but this is always done after measuring the impact on performance and carefully weighing that against the risk of data corruption. Unless you have done that, your "default" approach should be to avoid redundancies in your data model.

You haven't explained what your data model is supposed to mean, but I'm guessing you'll need something like this:

enter image description here

Note that statement has no title. You can get statement's title by JOINing Statement and SubSection.

NOTE: If you anticipate you'll need less or more than exactly two levels of sections, you can represent that via "adjacency list", as proposed by Mahmoud Gamal. For some other ways to represent the hierarchical data, take a look at this presentation by Bill Karwin.

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

Yes, normalize your tables.

You can do this using only two tables; the sections and subsections tables will be represented using only one table Sections with a new column ParentSectionNumber indicating the parent section for each section, the root section will have NULL as the parent section number. Something like this:

Sections:

  • SectionNumber,
  • SectionTitle,
  • ParentSectionNumber(Nullable) Foreign key references the same table.

For the second table, I am guessing that, there are statements for each section or sub sections, in this case create a foreign key that references the table sections:

Statements:

  • StatementNumber,
  • SectionNumber FOREIGN KEY references Sections(SectionNumber).

Upvotes: 1

Related Questions