Have Geek Brain

How to easily create a DB table / schema?

Is there an easy way to determine which fields and indexes are needed for each table in the application you are creating?

For example, if it is a webapp that simply allows people to create lists (any number of lists and users can create a "things to do" or "shopping list" list) and the user can assign other users to edit the list and is this list publicly available or just for certain users, how can tables be created so that they are very accurate and quickly designed? How about indices?

I did it in college and then asked the question again and got the method, but would like to know if there are standard and good ways to do it in the field.

0


a source to share


7 replies


Database design is complex ...

As with many things in life, this is a series of compromises. The first thing you need to decide is which DBMS you will be using (MySQL, SQL Server, Oracle, PostgreSQL, one of the "object-oriented" databases, etc.)

Then you need to make a decision to normalize v. insane JOINs to access your data. Consider questions such as "how much logic will I use in triggers, stored procedures, in application code, etc."



There is no "Quick'n'Easy" way to develop anything other than the most trivial databases.

'Of course, this is just my experience. YMWV.

+8


a source


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



I generally break my project into three parts (parts 1 and 2 happen ahead, and 3 are usually near the end of the project)
1) create tables based on relationships (parent / child / etc)
2) create fields based on content (parent has x attributes etc)
3) create indexes last based on how you fetch data from your tables

+3


a source


Have not 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 think the attributes speak for themselves. Thinking?

As far as indexes go, it just works with data. Any column that gets merged deserves an index (maybe even a clustered one). It's very ... dependent. But there are samples. But beyond optimizing for joins, many indexes are directly related to how the data is used, and this is not something that can be ensured by a rule of thumb. For example, if you are looking for users by pk and elsewhere by last_name, last_name deserves an index.

+1


a source


I think the decision is subjective. When I have to create tables, I look at the Java object that will represent that particular data model and from there. You will find many frameworks (Django, CakePHP, RoR) that you develop, and the framework will create the tables accordingly.

So, I would suggest evaluating what functions and data you need to store and develop from this table. Also investigate if there is a toolbox you have at your disposal to create tables for you from the object structure.

+1


a source


I would go for a simple (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 will depend on what are the actual most used queries and how they are executed. For example, if you are querying a lot on lists and list_items (most likely), you need an index for listid and name if you search by name.

Just some ideas. Hope they are helpful.

+1


a source


I would try not to lock up if you are still trying to figure out what works.

Just from your description, you need a table for your users' information, and also:

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 create things during design, the better. You can optimize later.

+1


a source


If you want to keep everything very simple and not too concerned about normalization. You can create one big table that stores the main object your webapp is based on, for example: lists and other smaller backing tables, links to a large table, for example: tbl_listType, tbl_permission, tbl_list_items).

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

-1


a source






More articles:


All Articles