Database Design

libra02

I have a quick question to ask. I am trying to see if I have a database correctly designed for a website. The website will be a review website on educational books - used by students / lecturers within a college. I have a feeling I could be missing something.

So I have a tables for
Members
Reviews
Book
Publiser
Genre
Author - at the moment I have said book can be writtten by only 1 person.

If I am correcty the cardinality all will be 1:M -ie Publiser can have many books but a book is only associated with one publisher. etc

Is my logic right

The Corinthian

libra02 wrote: »

Author - at the moment I have said book can be writtten by only 1 person.

If you intend to change this in the future, I'd still set up a 1:M relationship, even if you don't use it in the business logic, as it may be less of a pain later on.

If I am correcty the cardinality all will be 1:M -ie Publiser can have many books but a book is only associated with one publisher. etc

Is my logic right

Yes (as long as you don't do it backwards, of course). Is that what your requirements are?

libra02

The Corinthian wrote: »

If you intend to change this in the future, I'd still set up a 1:M relationship, even if you don't use it in the business logic, as it may be less of a pain later on.

Yes (as long as you don't do it backwards, of course). Is that what your requirements are?

The requirements are basically that you have a site where people can join browse book catalogue and leave reviews of the books.

Ok I see where you are coming from about authors,

Publisher's do you mean having the Many at publisher side. As nn my diagram I have the Publisher Entity with a line to Book Entity and crow feets are at the Book enitity.

Sorry if that sounds stupid but lack of sleep and flu is making me a bit stupid at mo.

So in my ER/ Entity Diagram some of the table I have derived so far

Author (author_id, name)
Genre ( genre_id, name)
Publisher ( publisher_id, name, country)
Book (id, title, author_id, publisher_id, genre_id, isbn, description)

The members and reviews will follow the same.

Im thinking if they are right and I gotten design they are probably Nomalised up to the 3NF already and then I can create and enter some test data etc.

The Corinthian

libra02 wrote: »

The requirements are basically that you have a site where people can join browse book catalogue and leave reviews of the books.

That's a mission statement at best; requirements are quite a different beast.

Im thinking if they are right and I gotten design they are probably Nomalised up to the 3NF already and then I can create and enter some test data etc.

There's nothing wrong with your design on a technical level so far; although, if reviews are going to be left and presumably managed by an administrator, it's clearly incomplete.

Your main problem is potentially that you have incomplete or inaccurate requirements - and I say potentially, because this is a question that cannot be answered here.

Now you can create a monster database design that allows for every potential eventuality (and inevitably fail) or you can go to a librarian (and/or whoever is commissioning this) and interview them on how they do it already, including things such as multiple publishers - or editions.

From that you can pin down actual requirements, of what is expected of your solution, and from that put together a technical specification and database design.

Nulty

I would make it a 1:M for authors, but allow for lots of characters in case there's a book with 4+ co-authors. 80+ chars?

libra02

The Corinthian wrote: »

That's a mission statement at best; requirements are quite a different beast.

There's nothing wrong with your design on a technical level so far; although, if reviews are going to be left and presumably managed by an administrator, it's clearly incomplete.

Your main problem is potentially that you have incomplete or inaccurate requirements - and I say potentially, because this is a question that cannot be answered here.

Now you can create a monster database design that allows for every potential eventuality (and inevitably fail) or you can go to a librarian (and/or whoever is commissioning this) and interview them on how they do it already, including things such as multiple publishers - or editions.

From that you can pin down actual requirements, of what is expected of your solution, and from that put together a technical specification and database design.

Sorry my bad. The requirments are as follows

Users can register with site.
Leave a review
Search by Genre, Author, Title, Publisher, ISBN

They are the main requirments. Basically it is going to be very small scale at the moment, kind of a trial basis and if successful it can be expanded. So that is why I have I statred small and just needed a bit of guidance from more experienced people that I have it correct at the moment


Members will post a review - I have a review table for them to be stored but I am a bit confued as to what you said about them being administrator managed. I have an idea what you might mean.

MyPeopleDrankTheSoup

Nulty wrote: »

I would make it a 1:M for authors, but allow for lots of characters in case there's a book with 4+ co-authors. 80+ chars?

Yeah but each author record will only have author so won't come near to 80 characters.


OP, a site like this is very common so my first port of call would be to Google and find someone else who did it. Good coders code, great coders re-use and all that.

Here's good site with schemas you can use:
http://www.databaseanswers.org/data_models/

And here's one for a bookstore that looks good for you:
http://www.databaseanswers.org/data_models/bookstore_and_warehouses/index.htm

The Corinthian

libra02 wrote: »

Users can register with site.
Leave a review
Search by Genre, Author, Title, Publisher, ISBN

They are the main requirments. Basically it is going to be very small scale at the moment, kind of a trial basis and if successful it can be expanded. So that is why I have I statred small and just needed a bit of guidance from more experienced people that I have it correct at the moment

Still not really requirements. What you have there is a bare-bones 'wish list', which is only slightly more than your previous 'mission statement'.

I note you're not bothering with book categories, edition or keywords - is are these concious choices or omissions?

If you're looking for guidance, then the first thing I'd suggest is fleshing those out in greater detail - in particular how book cataloguing is likely to work (i.e. what are the possible scenarios). Never presume you are an expert on the functionality of a solution you build - your job is to best codify the expertise of others, particularly those who will use it, not to presume you are an expert yourself.

Secondly other than the books themselves, you also have a number of other parts of the database you'll need to design; principally user management and book reviews.

Members will post a review - I have a review table for them to be stored but I am a bit confued as to what you said about them being administrator managed. I have an idea what you might mean.

Imagine allowing people to leave comments without having any ability to moderate them. Disaster.

Indeed, have you considered who will do the moderation? Is someone expecting you to do it or is there another designated person?

georgiecasey wrote: »

Good coders code, great coders re-use and all that.

That expression appertains more to re-using code you've previously written, not that others have written.

I get the impression that the OP is a student and still learning, so I would advise that they actually code it. Too many 'developers' end up relying on cutting and pasting other people's code far too early on in their careers and end up as bottom feeders in the industry, IMO.

Re-use code if you have to due to budget constraints or because you already know how to do it and couldn't be arsed. Until then, code it yourself.

libra02

The Corinthian wrote: »

Still not really requirements. What you have there is a bare-bones 'wish list', which is only slightly more than your previous 'mission statement'.

I note you're not bothering with book categories, edition or keywords - is are these concious choices or omissions?

If you're looking for guidance, then the first thing I'd suggest is fleshing those out in greater detail - in particular how book cataloguing is likely to work (i.e. what are the possible scenarios). Never presume you are an expert on the functionality of a solution you build - your job is to best codify the expertise of others, particularly those who will use it, not to presume you are an expert yourself.

Secondly other than the books themselves, you also have a number of other parts of the database you'll need to design; principally user management and book reviews.

Imagine allowing people to leave comments without having any ability to moderate them. Disaster.

Indeed, have you considered who will do the moderation? Is someone expecting you to do it or is there another designated person?

That expression appertains more to re-using code you've previously written, not that others have written.

I get the impression that the OP is a student and still learning, so I would advise that they actually code it. Too many 'developers' end up relying on cutting and pasting other people's code far too early on in their careers and end up as bottom feeders in the industry, IMO.

Re-use code if you have to due to budget constraints or because you already know how to do it and couldn't be arsed. Until then, code it yourself.

Ok I see where you are coming from now thanks.
Editions are not going to be bothered with at mo, genre's is what was requested. As I said this is going to be a beta site really. Testing the waters.

I know about moderating reviews, has that done but not built into the database.


Nope not a student - I wish. For a project that I could be involved in. I did a short course on databases a good while back as pary of a college course but it was years ago and it it was very basic but as I have "knowledge" I could be asked to get totally involved. So trying to get back into it.

Anyway thanks for the advice. Much appreciated.

MyPeopleDrankTheSoup

Nulty wrote: »

That expression appertains more to re-using code you've previously written, not that others have written.

I get the impression that the OP is a student and still learning, so I would advise that they actually code it. Too many 'developers' end up relying on cutting and pasting other people's code far too early on in their careers and end up as bottom feeders in the industry, IMO.

Re-use code if you have to due to budget constraints or because you already know how to do it and couldn't be arsed. Until then, code it yourself.

I meant to say that OP should do it themselves if they want to learn but if not for that, there's never a good reason to start with a blank page IMO. It's inefficient use of your time.

The Corinthian

georgiecasey wrote: »

I meant to say that OP should do it themselves if they want to learn but if not for that, there's never a good reason to start with a blank page IMO. It's inefficient use of your time.

If this is a once off project and they're not going to get into the business then I'd agree. Otherwise starting with a blank page is, more often than not, a very efficient use of your time in the long run.

With the advent of CMS's and other productized open-source projects, it has become very easy to build Web sites without really building them; a CMS, a few plug-ins and, maybe, a bit of hacked code.

Now everyone will use some 3rd-party code or plug-in from time to time, but if you begin with the approach that almost everything you do will be 3rd-party, then you'll never be able to do so from scratch and you'll never really understand what's going on under the bonnet.

This means you're forever doomed to be able to 'develop' only what has already been developed by someone else. You may be able to customize it, but there are serious limits to this and hacking code is dangerous when you don't really understand what it's doing.

The IT industry is full of such 'bottom feeders', so named because all they can do is standard, out of the box, solutions generally aimed at the cheap end of the market. Which is fine, if you're happy making a living that way, but you reach your limits very, very quickly then.

croo

The Corinthian wrote: »

you'll never be able to do so from scratch and you'll never really understand what's going on under the bonnet.

Why not? The great thing about those open source projects is that the code is there to look at and understand how it works. And I would think many would also learn from the design & implementations too; the very nature of how the projects evolve usually mean the design & coding is good... if not someone would usually improve them!

The Corinthian

croo wrote: »

Why not? The great thing about those open source projects is that the code is there to look at and understand how it works.

How can you understand how it works if you never learn how to code?

croo

The Corinthian wrote: »

How can you understand how it works if you never learn how to code?

I'm saying it can be useful learning tool. If you understand the problem and then look at how others have solved it then you can learn from it. We don't have to work out everything from scratch just to understand it. We can read a book to teach ourselves... likewise we can read the code to see how a problem was solved.

As an example, when I started to code, everyone effectively wrote their own database to persist data. Today nobody, except those who specialize in databases, cares how they work they just want to use them to persist the data and by doing so are much quicker at delivering. The same goes for application frameworks with the rails/scaffolding type frameworks allowing people to be extremely productive - for a certain types of projects admittedly. Beginning with a ready made open solution and customizing it to your needs is just an further extension of that.

Now I do admit that if you chose the wrong project - one that is badly designed and written - it could be a hindrance rather than a help. That risk can be greatly reduced if you select a popular & active FOSS project, it's unlikely to be rubbish as it's extremely unlikely that nobody involved has any idea.

The Corinthian

I understand what you're saying, I myself learned how to code originally typing out listings from a copy of Sinclair User and then subsequently hacking the code to bits.

But what we're talking about is somewhat different; someone who is doing, typically, commercial project takes short cuts and outside of basic coding knowledge (enough for a few hacks) will never really understand that open source code and will never really try to.

To use your database example, I will use a third-party database in projects rather than writing one, but I can write one because early on I did many things the hard way and learned how to do so. Instead today we have an industry full of 'developers' using third-party CMS's in projects rather than writing them, but they couldn't write one from scratch if they wanted to because they never took the time to learn by starting with "a blank page" anywhere.

Happens far too often, I'm sorry to say.

conor.hogan.2

Try to stumble your way through (Ruby on) Rails without thinking (or even worse not knowing) about DB relations and you will quickly be in a mess.

croo

The Corinthian wrote: »

But what we're talking about is somewhat different; someone who is doing, typically, commercial project takes short cuts and outside of basic coding knowledge (enough for a few hacks) will never really understand that open source code and will never really try to.

Yes, that is different okay.

What prompted my response was talk of "never knowing what's under the bonnet" when using open source (and other 3rd party libraries). While I felt/feel FOSS offers a good place for people to learn by actually looking under the bonnet. Of course you actually said "understand" but I read "know". I have assumed, perhaps naively if I understand you, that a developer has, at a minimum, the basics.

Delivery of the code is only the first step. On going support (and extension) is usually a much bigger aspect of any project, in the long run, and if you don't know what is under that bonnet you cannot deliver that support.

The Corinthian

croo wrote: »

I have assumed, perhaps naively if I understand you, that a developer has, at a minimum, the basics.

You need only read through some of the threads here to realize how 'basic' the knowledge of some is and the majority of the time they can get away with this because once they know how to install, configure and 'hack' a CMS, they're covered for the vast bulk of brochureware development.

Where this becomes an issue is when there is no third-party product that can fulfil a client's requirements; the problem is that if those requirements cannot be achieved with a few hacks, they are caught in a situation whereby they either are forced to develop something that is far beyond their coding knowledge, outsource it or to tell the client it cannot be done.

That's why I use the term bottom feeders; they occupy a crowded segment at the bottom of the IT food chain that is easy to enter, but limited in scope and so they tend to differentiate on the basis of graphical design rather than technical skills if they can.

Delivery of the code is only the first step. On going support (and extension) is usually a much bigger aspect of any project, in the long run, and if you don't know what is under that bonnet you cannot deliver that support.

You don't really need much, if any, coding knowledge to maintain a CMS-based site - the open source community does that for you and the most you have to do is cut and paste your hacks back into it whenever you upgrade.

My objection was to the misuse of the expression "good coders code, great coders re-use" as a means to justify using third-party products rather than writing your own. If you follow that philosophy, you'll never become a good coder, let alone a great one.