November 11, 2015 at 5:29 pm
hello
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 )
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)
________________________________________
November 12, 2015 at 10:23 pm
Without knowing exactly what you currently have and what is the objective, on first glance it looks like the list of attributes you have provided belong to more tha one enyity which would suggest that he table needs normalising.
For instance "Date of Work" surely this is an attribute belonging to a piece of work not the Author, unless of course you are saying an author can only produce a single piece of work, similarly the ISSN you mention I think should be International Standard Stationary Number which would be the attribute of a book not the Author.
Also you have a column named Dates which suggests you will be storing more than one date surely poor practice also.
...
December 16, 2015 at 8:44 am
I always thought that library systems / card catalogs were an interesting study in database design requirements.
Maybe it wouldn't be a terrible idea to see what historically has been done <JUST AN OPINION HERE>. I see quite a difference between bibliographical systems and what routinely falls into relational designs, but I haven't really followed the subject much since I started sql stuff.
February 3, 2016 at 2:37 pm
Perhaps this might help get you started.
http://www.sqlservercentral.com/Forums/FindPost1758249.aspx
----------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply