SQLTeam.com | Weblogs | Forums

Need help on database design



i have a huge database of library system but i have a problem on how to design tables on database 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 foreign key for each repeatable data or this will be too many tables and may affect performance ..

taking into consideration that AUTHOR is only a small entity from about 30 entities 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 )

(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)


First, you should definitely have separate Entities for "Work" and "Institution", at least. Those are not part of the Author table. I think Music is just a subtype of Work, rather than a completely separate Entity, but I can't be 100% sure just from what's stated.

Next, yes, for (nearly) all of those, you'll need separate tables. If there can be multiple titles for an Author, those should be in a separate table. Since there are multiple "dates associated", those should also be in a separate table. And so on.