Need help on database design

Question

I have a huge database of library system but I have a problem on how to design tables on this database. This is because I have under one entity (for example AUTHOR) a lot of repeatable fields. So I don't know whether to make a table with a foreign key for each repeatable data or that this will result in too many tables and may affect performance.

Taking into consideration that AUTHOR is only a small entity with about 30 columns like author or somehow bigger.

So is it suitable to make about 10 tables for author only?

I know that I will make all the Non Repeatable in one table called Author but the big problem is for the rest of fields.

For example:

• Author ( Repeatable )

NOTE

(R) ---> Repeatable

(NR) ---> Non repeatable

indicator means a character or digit which means some specific data about an author

First Indicator Type of personal name entry element 0 --> Forename 1 --> Surname 3 --> Family name

Second Indicator Type of added entry 0 --> No information provided 2 --> Analytical entry

---

Sub-field Codes

• a - Personal name (NR)

• b - Numeration (NR)

• c - Titles and other words associated with a name (R)

• d - Dates associated with a name (NR)

• e - Relator term (R)

• f - Date of a work (NR)

• g - Miscellaneous information (R)

• h - Medium (NR)

• i - Relationship information (R)

• j - Attribution qualifier (R)

• k - Form subheading (R)

• l - Language of a work (NR)

• m - Medium of performance for music (R)

• n - Number of part/section of a work (R) • o - Arranged statement for music (NR)

• p - Name of part/section of a work (R)

• q - Fuller form of name (NR)

• r - Key for music (NR)

• s - Version (NR)

• t - Title of a work (NR)

• u - Affiliation (NR)

• x - International Standard Serial Number (NR)

• 0 - Authority record control number or standard number (R)

• 3 - Materials specified (NR)

• 4 - Relator code (R)

• 5 - Institution to which field applies (NR)

• 6 - Linkage (NR)

• 8 - Field link and sequence number (R)

---

Answer 1

I suggest you build seperate tables for all repeatable data. This is good database design and the DBMS you are using should be able to handle it. It provides you with these benefits:

• You arrange data into logical groupings such that each group describes a small part of the whole.
• You minimize the amount of duplicate data stored in your database.
• You organize the data so when you modify it, you make the change in only one place.
• You build a database in which you can access and manipulate the data quickly and efficiently.
• Your database is better maintainable in case your data model changes.