PDA

View Full Version : Gah, how do you go about designing databases?




Cromulent
Jul 16, 2009, 04:04 PM
I've been working on this database for awhile now but every time I get reasonably far in to the design process I discover there is something that I have either overlooked or simply not considered. Now I appreciate that some of this is simply lack of experience and just needs to be worked through, but there must be some tools available be that books or software that help the process along.

Does anyone have any recommendations (books, software, tutorials, websites or papers) at all for designing relational databases in an effective manner?



afd
Jul 16, 2009, 04:24 PM
I would recommend http://www.businesslogical.co.uk/ if you are after any Filemaker training. Did a 2 day course there. Bit out of the way though.

Cromulent
Jul 16, 2009, 04:33 PM
I would recommend http://www.businesslogical.co.uk/ if you are after any Filemaker training. Did a 2 day course there. Bit out of the way though.

Thanks for the information but I'm using PostgreSQL and I am not sure how much Filemaker knowledge translates. Is Filemaker even a proper ANSI SQL compliant relational database?

GorillaPaws
Jul 16, 2009, 04:53 PM
I'm in the same boat as you. I just ordered a copy of Beginning Databases with PostgreSQL: From Novice to Professional (http://www.amazon.com/gp/product/1590594789/ref=ox_ya_oh_product) a couple of days ago and am still waiting for delivery. It had some good reviews, so I bit the bullet. I'd certainly welcome any other info/resources for learning good relational database design rules/strategies/techniques that any of you could provide.

Cinder6
Jul 16, 2009, 04:57 PM
Read up on the entity-relationship model. It'll help you map out exactly which attributes you need, and where they should go. Then read up on normalization, particularly the three normal forms.

After that, design your own ER diagram. Take note of any constraints you need to place on the system, functional dependencies, and all that jazz. Then you can go about the actual SQL work.

AlmostThere
Jul 16, 2009, 05:19 PM
Can you fill in some details about the purpose of your database and the sorts of problems you are having?

Databases are designed differently for different purposes. A simple relational database driving a blog is very different from the databases or data warehouses used to store massive volumes of archived data which are very different from databases supporting high frequency transactional processing which are very different from the design of databases used for storing variable and unstructured data.

Joe Celko has written several good books.

Also, much like programming, there will always be things you haven't considered - but every model is an approximation and part of the process is understanding when you have 'done enough'.

craig1410
Jul 16, 2009, 05:24 PM
Hi,
Some advice I would offer is to learn about normalisation and attempt to achieve as many normal forms as possible but always be prepared to denormalise where necessary. Don't pursue perfection because it doesn't exist, except in training courses...

I have worked with some hugely complex databases at the heart of the Oracle E-Business suite and I was initially surprised at how they were designed because I kinda expected them to be perfect and they were far from it. However, what I learned is that it is the end result which is important and there are many reasons why perfect normalisation is not always necessary or indeed desirable. Think in terms of entities (eg. Product, Employee, Department, Order, Invoice) and think carefully about naming these core entities as it will make it easier to understand later.

If you are programming in an object oriented programming language/platform then you will probably want to consider using or designing an object-relational mapping (ORM) layer. This allows you to de-couple the peculiarities of database design from the design of your applications which largely allows you to focus less on database design nuances and focus more on application functionality. Ruby On Rails makes a lot of use of ORM and it is compelling once you get a taste of it. Database tables are only required to store persistent objects so focus on the objects and create code in the ORM layer to simply store and retrieve these objects from the tables. There is a bit more to it than that but start simple and introduce complexity only when needed.

Also, databases are hugely powerful these days and the hardware and storage is cheap and powerful too so there is often no need to be a perfectionist on the database design. Don't be lazy or wasteful but focus on the application and the business logic first and foremost and fix performance problems when/if they appear. Read up on agile programming techniques and deliver iterations of your application regularly (daily is good) - that way you shake out fundamental design changes early and avoid massive redesigns.

Above all, relax and enjoy!
Craig.
:)

SRossi
Jul 16, 2009, 05:36 PM
Does anyone have any recommendations (books, software, tutorials, websites or papers) at all for designing relational databases in an effective manner?

I have a Handout from my college that delves into Databases it covers normalisation and the such. And since it is from college it is well written but will not cover everything although it may be start if you wish?

I also have a book that is a practical introduction to SQL and I know that isn't what you have asked for but I believe that it is a superb book and if you would like me to send you a copy just ask.

Hope this can help you.

Stephen

HiRez
Jul 16, 2009, 05:59 PM
Designing databases is an art (and in many cases) a career in and of itself. It's easy to underestimate the complexity, as you found out. There are a lot of books written on the subject, one of my favorites is Database Design for Mere Mortals (http://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0201752840/ref=sr_1_1?ie=UTF8&s=books&qid=1247785089&sr=1-1), it's like 12 years old (but updated in 2003) but all of it still applies since it deals in the abstract of the design and not code for a specific database engine (in fact there is only about 1 page of SQL in the book). And there are some other good books out there too. If you're going to be using databases a lot in your apps it's worth putting in the time of learning the design because it's really important and it's something that can cost you a lot of time if you need to go back and fix something late in the design process.

larkost
Jul 16, 2009, 06:12 PM
Just as a note: FileMaker is not based in SQL, but has its own way of thinking about things. It is neither better or worse (generally), but has its own strengths an weaknesses. I have done both, and both are good in different places. But they are different mind-sets.

Cromulent
Jul 17, 2009, 08:20 AM
Thanks for the information.

In response to the person who asked for more information about the database, currently I have the following tables:

user
character
quests

and numerous others that follow the following design pattern:

user has the following fields:

user_id (PK)
user_name (unique)
password
email

etc etc

character has the following fields:

character_id (PK)
user_id (FK)
character_name (unique)

etc etc

The idea was that you can find any information that you require with just two queries. If you have the character_id you can find the user_id and if you have the user_name or user_id you can find all the character_ids associated with that account. The idea was to reduce the number of queries needed to find information. All tables follow this structure.

It just seems somewhat messy. It is primarily a read only database, writes are going to be infrequent in comparison to reads so I need to make sure that data can be accessed with the fewest number of SELECTs possible.

Hopefully that gives you a better idea of what I am trying to do.

SRossi
Jul 17, 2009, 08:41 AM
Did you do any design before starting the database?

I ask because I feel that jumping into creating a database without considering a slight bit of design can lead you to all sorts of problems. As you now have.

Consider looking at relationships between your entities, this will allow you to figure out how all the entities will relate to each other thereby allowing you to see how easy it will be to query between the entities.

As I said in my other post I can send you a document that shows you how to normalise and also how to set the relationships between each of the entities that you will find.

Hope this helps somewhat.

Stephen

lee1210
Jul 17, 2009, 08:53 AM
If you have references from one table to another by way of a foreign key, or if you related these things with a join table in the case of many-to-many relationships, etc. you should be able to get this with a single query using JOIN or simply joining tables with WHERE. For example:

SELECT user.id,user.name,character.id,character.name
FROM user,character
WHERE user.id = 3
AND character.user_id=user.id
ORDER BY character.name;


or


SELECT user.id,user.name,character.id,character.name
FROM user
JOIN character ON (user.id = character.user_id)
WHERE user.id = 3
ORDER BY character.name;


This is the point of foreign keys, normalization, etc. If you do something very frequently you can also build views that pull together data from a number of tables so you don't have to explicitly join each time you need to get the data.

Do abide what others have said. You don't design a database at the psql prompt, you design it on paper or in a tool made for database design. Read the things linked, and start completely from scratch. This will mean throwing away your current schema and code that accesses it, but that isn't a bad thing. Once you have a design, and know your relationships (one-to-one, one-to-many, many-to-many), what goes where, etc. actually writing the DDL to generate your tables should be the easy part.

Also note that in the queries above i took out the redundant user_ and character_ prefixes on fields. When you read something aloud, it makes things a little clearer. You wouldn't say "I need my character's character name", you'd just say "I need my character's name". Save the prefixing of table names for your foreign keys.

-Lee

Cromulent
Jul 17, 2009, 09:48 AM
Thanks for the advice. Obviously I was being somewhat nieve in my previous attempts. Still it was always meant as a learning exercise so I guess that is a good thing.

I'll sit down and really try and hammer out what I need from this database. Unfortunately half the problem is that I want the database to remain generic so that other programmers can extend the design and add their own specific features. That makes the whole process quite difficult on both the SQL side and the C side. I have yet to see if I have bitten off more than I can chew.

I probably have, knowing me.

afd
Jul 17, 2009, 01:38 PM
Thanks for the information but I'm using PostgreSQL and I am not sure how much Filemaker knowledge translates. Is Filemaker even a proper ANSI SQL compliant relational database?

Sorry I'm not even sure I understand your question! I think might be working on different database types . .

ChrisA
Jul 17, 2009, 02:28 PM
I've been working on this database for awhile now but every time I get reasonably far in to the design process I discover there is something that I have either overlooked or simply not considered. Now I appreciate that some of this is simply lack of experience and just needs to be worked through, but there must be some tools available be that books or software that help the process along.

Does anyone have any recommendations (books, software, tutorials, websites or papers) at all for designing relational databases in an effective manner?

Of course the rules about "normalization" apply. But the #1 thing about designing a database is that it will change over time. What you want is a design that is adaptable. Normalization helps a lot in this way but then you can go nuts that way and end up with 200 very naror tables and all of querries would be 8-way joins So you compromise. But don't compromise on adaptability.

The disater you are trying to avoid is a few years from now after yu have many, many lines of code writen and then you have to add some new features that change the database in a way that brakes 50,000 lines of code.

A good rule of thumb is to make a table (or "relation") for each physical kind of think. A "person" table, "Vehicle" table and so on. If tables corespond 1:1 with classes of real things you can't go to far wrong.

As for books. They do not have to be PostgreSQL specific. You want books on DBMS theory, not on how to run your specific DBMS system. These books will be using a generic SQL in their examples

scotty96LSC
Jul 17, 2009, 02:58 PM
We build Filemaker solutions here. The very first thing is a big white board and draw out the build. Even then you miss some things but this does significantly help eliminate a lot of rework/rethinking.

adrian.oconnor
Jul 17, 2009, 03:05 PM
I've read most of the replies in here, and from what I can see you are definitely on the right track. You absolutely should normalize your tables -- that user_id column in the characters table is very important.

If you don't want messy SQL in your lovely C program then you should use Postgres functions - they are a very useful feature of Postgres. Here's how you create a procedure that joins your tables and returns the results based on an input parameter:


CREATE OR REPLACE FUNCTION GetCharactersForUser(TEXT) RETURNS SETOF characters AS
$BODY$
BEGIN

RETURN QUERY
SELECT c.*
FROM characters c
INNER JOIN users u
ON u.user_id = c.user_id
WHERE user_name = $1
ORDER BY c.name
LIMIT 100;

END
$BODY$
LANGUAGE 'plpgsql';


Now you just need to call "select * from GetCharactersForUser('adrian.oconnor')" in your C code. Take a look at the <a href="http://www.postgresql.org/docs/8.4/static/sql-createfunction.html">postgres manual page for 'create function'</a> for more.

The only downside to functions is that if you decide to switch to the Sqlite embedded database engine at some point they aren't supported at all. MySQL support for procedures is limited. Postgres is a really good choice though (not least because you'll find it easy to switch to Oracle if you're used to writing pgsql functions and procedures).

As a note of advice (that you are welcome to ignore), I would recommend that when you join two tables you ALWAYS use the INNER JOIN syntax -- don't be tempted to do SELECT * FROM table1, table2 WHERE table1.pk = table2.fk.

Keep at it! The only way to get good at database design is to do it - you'll learn something with every database that you design. And my last bit of advice is don't worry about supporting future features until you need them. While you're learning, thinking about that kind of stuff just gets in the way of the important thing -- getting a working system up and running.