How to design DB table / schema with ease?

Question

Is there a simple method to decide on what fields and indexes are needed for each table in an app you design?

For example, if it is a webapp that simply lets people create lists (any number of lists, and users can create "things to do" list or "shopping" list), and the user can assign other users to edit the list, and whether the list is viewable publicly or to only certain users, how can the tables be design so that it is very accurate and designed quickly? What about the indexes?

I did that in college and then revisited the question some time ago and have a method, but would like to find out if there are standard and good ways to do it out in the field.

Answer 1

Database design is hard ...

As with many things in life, it's a series of tradeoffs. The first thing you need to decide is what DMBS you will use, (MySQL, SQL Server, Oracle, PostgreSQL, one of the "Object-oriented" databases, etc.

Then you need to decide on normalization v. insane numbers of JOINs to get to your data. Questions like "how much logic will I implement in triggers, stored procedures, in app code, etc" need to be addressed.

There is no "Quick'n'Easy" way to design anything but the most trivial of databases.

'Course, that's just my experience. YMWV.

Answer 2

it is beyond the scope of this answer to fully explain database design

I generally break my design into three parts (part 1 and 2 happen up front, while 3 is usually near the project end)
1) create the tables based on relationships (parent/child/etc)
2) create fields based on content (parent has x atributes, etc)
3) create indexes last based on how you select data from your tables

Answer 3

Haven't heard of any formal approaches to this problem but there are rules of thumb. All nouns and business objects become tables, normalized of course. And I'd think the attributes sort of speak for themselves. I guess?

As for indexes, it just comes with working with the data. Any column that's joined off of deserves an index (maybe even clustered). It's very... depends. But there are patterns. But other than optimizing for joins, many indexes are directly related to how the data is used, and this isn't something that can be provided by rule of thumb. Like if you look up users by pk and elsewhere by last_name, last_name deserves an index.

Answer 4

I think the solution is a subjective one. When I have to design tables I look at the Java object that will represent that particular data model and go from there. You'll find a lot of frameworks (Django, CakePHP, RoR) have you develop the model and the frameworks will build the corresponding tables.

So I would suggest evaluating what functionality and data you need to store and develop your tables from that. Also look into whether the tool set you have at your disposal offers to generate the tables for you from the object structure.

Answer 5

I would go for the straightforward (almost) normalized design:

CREATE TABLE lists (
                    listid serial, 
                    name varchar, 
                    ownerid int references users(userid)
                   )

CREATE TABLE list_items (
                         listid int references lists(listid), 
                         value varchar, 
                         date datetime
                        ) 

CREATE TABLE permissions (
                          permissionid serial, 
                          description varchar,
                         )

CREATE TABLE list_permissions (
                               listid int references lists(listid),
                               permissionid int references permissions(permissionid)
                               userid int references users(userid)
                              )
CREATE TABLE users (
                     userid serial,
                     name varchar
                   )

Which indexes to create would depend on what are the actual most used queries and how are they performing. For instance, if you query a lot on the lists and list_items (likely) you'd want an index on listid and on name, if you'll be searching by name.

Just some ideas. Hope they're helpful.

Answer 6

I'd try not to lock yourself in if you're still trying to see what works.

Just from your description, you'd want a table for your users' information, as well as:

tbl_lists:
    ID_list (primary key)
    UserID (foreign key to list owner)
    ListName

tbl_listItems:
    ID_listItem (primary key)
    ListID (foreign key to list)
    ItemDescription

tbl_permissions:
    ID_permission (primary key)
    ListID
    UserID (foreign key to user you're granting permission to)
    PermissionTypeID (what kind of permission)

tbl_permissionTypes:
    ID_permissionType (primary key)
    Description ("can view", "can edit", etc.)

The more flexible you can make things while you're designing, the better. You can optimize later.

Answer 7

If you want to keep things very simple and are not too concerned with normalizing. You could create one big table that stores the main object your webapp is based around, ex: lists, and have other smaller supporting tables link to the big table, ex: tbl_listType, tbl_permission, tbl_list_items).

Then when you write queries, you almost certainly include the main table and you can link in other supporting tables for more granular details.