Designing a database table structure. Advice appreciated.

[Maximus Primal]

Hi.

I am trying to design a database to replace a Purebasic program I wrote over 1 1/2 years ago which held my magazine collection.

My original program was complicated and hard to update, as I needed to make many changes to test a theoretically small change. Now I have access via a home use license at work, I am looking to create something in this which is more flexible and easier to update/maintain.

Something to point out is that when it comes to the magazines. I need them effectively stored or listed by: Magazine, then country, then issue/publication date. Everything after that is specific to that issue.

I have sat down with Word, and created a break down of what I need in the way of fields/entries to hold the information I want about any given magazine. I have also split this into sections where the parts are related to each other on a specific level. So stuff that relates to all copies of a given magazine issue (cover price, pages etc), and stuff the relates to the specific issue I have (condition, where/when obtained etc).


But I am a little confused with the concept of normalization, which I understand to be breaking the database into common parts to save duplication of data.

I have attempted to recreate what I think is the right structure in tables and used relationships to link them together, but I would be very grateful if someone can someone look at the images attached and tell me if I have broken this down right and if this would work as a structure for a database before I go any further and find out I am going to hit major problems have to start again.

I do not need to know how to create the database structure itself, ie. what to put in the tables as I would rather work that out myself and ask for help if I hit a problem. I just really need to know whether I have got the initial design right.

Thank you
Max

 

[SteveSchapel]

Max,

There appear to be some problems with your data schema.

There is some stuff I don't understand, of course, and as such not really able to comment specifically.

You are relating Publisher to IssueDetails. Does that reflect the real-life relationship between publisher and magazine? That there would be different publishers for different issues?

Unless I am misunderstanding, the Countries table is wrong. The way it is set up at the moment, it looks like you could only ever have one magazine per country.

The relationship structure between Publication, Specifics, and IssueDetails, appears to be incorrect. I would imagine the Specifics should relate to a specific Issue, not directly to the Publication. But then, I don't fully understand the concept of Publication here as distinct from Issue - it kinda looks to me like Publication and IssueDetails should maybe really all be in the one table.

 

[Maximus Primal]

Max,

There appear to be some problems with your data schema.

There is some stuff I don't understand, of course, and as such not really able to comment specifically.

You are relating Publisher to IssueDetails. Does that reflect the real-life relationship between publisher and magazine? That there would be different publishers for different issues?

Yes that is correct. A magazine may be a) Published by different companies in different parts of the world under licence (Much as magazines like Vogue, Marie Claire, FHM are), also b) A magazine may change publisher at various points in it's lifespan if the title is sold, so "Magazine A" may originally be published by EMAP but 4 years down the line be sold to IPC after Emap decide it is not suitable for their current portfolio for example.

So the April 2010 issue may have been published by Emap, but the May 2010 by IPC after Emap sold it too them.

I hope that makes sense.

Unless I am misunderstanding, the Countries table is wrong. The way it is set up at the moment, it looks like you could only ever have one magazine per country.

I did not realise that. Thank you, that is exactly why I wanted to post this now, before I found I serious flaws. Could you elaborate on why you say I can only have one issue per country so I can understand what I did wrong?

The relationship structure between Publication, Specifics, and IssueDetails, appears to be incorrect. I would imagine the Specifics should relate to a specific Issue, not directly to the Publication. But then, I don't fully understand the concept of Publication here as distinct from Issue - it kinda looks to me like Publication and IssueDetails should maybe really all be in the one table.

Okay to try and explain why I did what I did and if it is wrong please do tell me again so I can go back to the drawing board.

tbl_Magazines would hold the name of the magazine : Vogue, FHM, More or whatever it is called.

under each magazine title you have the country (tbl_Countries). So you are showing which country's version of the magazine you have: UK, USA etc

Next comes tbl_Publication. This is the issue information. What issue number (if any), and the publication date and year. This enables me to specify the actual issue I am dealing with.

From this you have two branches. tbl_Specifics - stuff speciffcally relating to the copy I have (condition, damage, where I got it). A second branch contains information on the actual issue which is always the same (cover price, pages, cover star, the actual publisher (in case as above, it changes publisher part-way through it's lifespan).


This is what I was trying to achieve and it seems have failed to. If I am wrong can you or someone advise me where I should be looking and what the fundamental flaws in what I have done are so I can relook at it and try and get it right. As I said originally, I do not want the solution - only a guide on where I have gone wrong.

Thank you
Max

 

[Maximus Primal]

I am hoping someone answers this and I REALLY having a hard time understanding how to normalize a database structure for a magazine collection as a lot of it does not inter-relate as such.

I have been reading the example at www.dbnormalization.com which as been a massive help as it does not go on about sales, and customers etc as most examples seem to (not all databases are for business use after all).

I have come up with this so far:

2nd Normalization
Magazine Table
• Magazine Number  Primary Key
• Magazine Title
• Country ID
• Issue Number (if any)
• Publication Date
• Publication Year
• MagType ID

Magazine Type Table
• Magtype ID
• Magazine Types / Categories

Country Table
• Country ID
• Country of Publication

Format Table
• Format ID
• Format Type (Magazine / Digital / Both)

Publisher Table
• Publisher ID
• Publisher Name

Condition Table
• Condition ID
• Condition (20 options, 10-0 in .5 steps)

Models Table
• Cover Models ID
• Name of Cover Model

Magazine Details Table
• Magazine Number
• Cover Price
• Pages
• Barcode Number
• Publisher ID
• Cover Model ID
• Cover Scan
• Format ID
• Condition ID
• Condition Notes
• Obtained From
• Obtained On
• Obtained Price
• Keywords for referencing articles etc

The blue ones are where I have created a look up table to reference the answer from and store a relative value instead.

This is the best I can get based on my interpretation of the second level of normalization. I have looked and looked at the third and cannot see way to structure this into what it required. I have an issue of redundancy in respect of the magazine type and country, but I cannot do it any better than above.

I can split the Magazine Details table into two separate parts, but I am not sure if it is worth it or necessary.


If anyone has any comments on this and if I am on track or not, please. please let me know as I am well out of my depth here and only just managing to keep my head above the water so to speak.

 

[Steve R.]

Much better. Your tables "Magazine Table" and "Magazine Details Table" can be combined into one table.

I don't know how much detail you are planning, but you may want to include fields for Authors, Artists, Stories, and cover art (image). <<-- Same as the cover model?

You may be missing a table for Magazine Name. Not the same, as Magazine Type. I assume that you are dealing with more than one magazine (ie: Newsweek, Time, Business Week, etc.) This may be your intent with the Publisher Table.

Are there any embedded articles (Stories)?

See my posts: Analog Science Fiction And Fact Database and Science Fiction Book Database

 

[Maximus Primal]

Much better. Your tables "Magazine Table" and "Magazine Details Table" can be combined into one table.

Really my starting point was to separate those two, splitting what effectively where the "magazine" and "the information" apart which I took to be the first step.

I don't know how much detail you are planning, but you may want to include fields for Authors, Artists, Stories, and cover art (image). <<-- Same as the cover model?

Not as such, with regards to authors/articles I would be happy for them to be in a generic field. So the user stores what is interesting or of importance to them. The vital information is more the cover model and condition of the magazine.

You may be missing a table for Magazine Name. Not the same, as Magazine Type. I assume that you are dealing with more than one magazine (ie: Newsweek, Time, Business Week, etc.) This may be your intent with the Publisher Table.

Yes I see that. But for the purpose of where I got at 11pm I could not see it at the time (or I viewed it as not-vital for late at night). It could be bolted on when I start the physical table design. But thanks for the heads up, at least I know I missed something.

Are there any embedded articles (Stories)?

Within the database? No. Just notes on anything of interest. The only external data would be a link to the cover image as I have seen that storing physical images is not good due to the massive increase in file size they would cause (and bear in mind I have over 600 magazines, this would probably cause my own database to exceed the 2-4gb limit quite easily).

I may later add links to the magazine's website and the publishers website, but again at this point it is not important.

See my posts: Analog Science Fiction And Fact Database and Science Fiction Book Database

[/quote]

They look very interesting and I can see similarities in what you have done there and what I am looking to create. I will take a closer look when I get home from work when my head is more awake (only been up an hour and spend 40 mins of that smashing my head against my ISP's "technical support" when my email failed to work ).

Thanks for the help.
Max

 

[Maximus Primal]

You could download a Book database template and see how they modeled it. http://office.microsoft.com/en-us/templates/book-collection-database-TC001018643.aspx
It might be worth looking at other collections also i.e. DVD just to get ideas. You may also want to close your field names: Cover Model ID
CoverModelID

Thank you. I have downloaded a couple of the examples from there to look at later to see if they give me any ideas.

My table names are (at this point) for my reference so I understand how I am breaking them up. I would change them to something more suitable later. I understand from other posts that you should not use spaces or special characters in field/table names to avoid any possible issues.

Max