Gah, how do you go about designing databases?

Discussion in 'Mac Programming' started by Cromulent, Jul 16, 2009.

  1. macrumors 603

    Cromulent

    Joined:
    Oct 2, 2006
    Location:
    The Land of Hope and Glory
    #1
    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?
     
  2. afd
    macrumors 6502a

    Joined:
    Apr 12, 2005
    Location:
    Scotland
    #2
  3. thread starter macrumors 603

    Cromulent

    Joined:
    Oct 2, 2006
    Location:
    The Land of Hope and Glory
    #3
    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?
     
  4. macrumors 6502a

    GorillaPaws

    Joined:
    Oct 26, 2003
    Location:
    Richmond, VA
    #4
    I'm in the same boat as you. I just ordered a copy of Beginning Databases with PostgreSQL: From Novice to Professional 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.
     
  5. macrumors 6502

    Cinder6

    Joined:
    Jul 9, 2009
    #5
    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.
     
  6. macrumors 6502a

    #6
    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'.
     
  7. macrumors 6502a

    Joined:
    Mar 22, 2007
    Location:
    Scotland
    #7
    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.
    :)
     
  8. macrumors regular

    Joined:
    May 27, 2009
    Location:
    Glasgow, Scotland
    #8
    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
     
  9. macrumors 601

    HiRez

    Joined:
    Jan 6, 2004
    Location:
    Western US
    #9
    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, 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.
     
  10. macrumors 6502a

    Joined:
    Oct 13, 2007
    #10
    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.
     
  11. thread starter macrumors 603

    Cromulent

    Joined:
    Oct 2, 2006
    Location:
    The Land of Hope and Glory
    #11
    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.
     
  12. macrumors regular

    Joined:
    May 27, 2009
    Location:
    Glasgow, Scotland
    #12
    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
     
  13. macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #13
    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:
    Code:
    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

    Code:
    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
     
  14. thread starter macrumors 603

    Cromulent

    Joined:
    Oct 2, 2006
    Location:
    The Land of Hope and Glory
    #14
    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.
     
  15. afd
    macrumors 6502a

    Joined:
    Apr 12, 2005
    Location:
    Scotland
    #15
    Sorry I'm not even sure I understand your question! I think might be working on different database types . .
     
  16. macrumors G4

    Joined:
    Jan 5, 2006
    Location:
    Redondo Beach, California
    #16
    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
     
  17. macrumors 65816

    scotty96LSC

    Joined:
    Oct 24, 2007
    Location:
    Charlotte, NC
    #17
    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.
     
  18. macrumors 6502

    Joined:
    Jan 16, 2008
    Location:
    Nottingham, England
    #18
    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:

    Code:
    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.
     

Share This Page