New topic: Good Database Design
<http://forums.realsoftware.com/viewtopic.php?t=47184> Page 1 of 1 [ 2 posts ] Previous topic | Next topic Author Message neonash7777 Post subject: Good Database DesignPosted: Wed Mar 06, 2013 11:51 am Joined: Mon Nov 29, 2010 7:01 pm Posts: 411 I've been mulling over a few design methods for databases and I was wondering if anyone experienced with databases could give me some advice. 1) Right now a lot of my tables have similar columns: Id CreatedDate ModifiedDate Name etc... --------- Every time I make a new table I have to include these columns and reset them up. In addition there are a lot of generic functions I run on my database's object tables that require these standard columns. For example, every time you're viewing an object in the program it shows the "Name" field as the title, so it always needs to be there. The annoying thing is that sometimes I need to add to this list of standard columns and I don't want to update every single table with say a 'CreatedBy' field. So I had this idea to create a generic solution... My idea was to have a generic table, something like "ObjectsTable" with: Id CreatedDate ModifiedDate Name CreatedBy Table TableId ----- Where table is almost like a reference to the 'class' of the object and tableId a reference to the 'instance' of the object. For example, I might have Id - 1 Name - Joe Table - Contacts TableId - 2 -------- And then on the contacts table I might have Id - 2 Email - [email protected] Phone - 1(555)-555-5555 Address - etc... That way if I ever expand the fundamental structure of an object, it will be very simple to do. The downside would be the use of JOINS when querying things. I suspect I could fix this using views. Would this be a good design? 2) Another thing I've been struggling with and contemplating over is privacy of rows. I have a few ideas. One was to add a 'privacy' field to every table, but I'm leaning against this. Currently most object are accessible if you're given access to a view that contains them. Someone can edit an object if: CreatedBy or AssignedTo is their userId Or if the table is one their profile has full access to. The problem with this model is that sometimes you need access to only some rows on a table, but they may need more than two people to have access to editing them. I fixed this using a "GroupId" field. And if someone is a user in the group then they can edit the object. The problem with this model is that you need to create a new "Group" every time you need a new configuration. Not only that but there's no good way to tell if the set of people you want to give access to already exists as a group for you to choose from. Anyways, I'm sure theres a more elegant solution than the one I've been using. One idea was to get rid of the "CreatedBy" and "GroupId" fields and instead have an access table. ====== "Access" Table TableId IsCreator? CanView? CanEdit? CanDelete? etc... Would this be a good idea? Is there a better way? Top Bob Keeney Post subject: Re: Good Database DesignPosted: Wed Mar 06, 2013 1:21 pm Joined: Fri Sep 30, 2005 11:48 am Posts: 3449 Location: Lenexa, KS Part 1: Regardless of the similarities, there are some really good reasons to have CreatedDate, ModifiedDate, CreatedBy, ModifiedBy in every row of each table. This makes queries much easier rather than having to join two separate tables for queries. Your solution simply moves it from part of every record into a table with a LOT of rows. The real drag of having those fields in every table is filling them in for every transaction. That's part of the reason why we've gone to using ActiveRecord http://www.bkeeney.com/rbinto/activerecord/ and placing the appropriate code into the ActiveRecord events (BeforeCreate/BeforeSave). AR makes it easy to have all this relevant code in one place/class. Another thing that we've done for a while now is not to have a generic 'id' field for each table. Doing joins requires the results to aliased. If a table is named "People" the primary key field is named "People_ID". There's no confusing which table the id belongs to then in a join. So if you saw an SQL Statement "Select person_id, firstname, lastname, group_ID, groupname from tblPerson, tblGroup WHERE blah" there's no mistaking which ID you're referring to. Naming conventions kind of suck, but I prefer to code for clarity not just for now but for five years from now when I have to tweak something. Part 2: This is a complicated idea. Having security groups is okay but the solutions I've seen usually require three tables to accomplish it. Users, Groups, UserGroups and if there's private data you don't want some people to see you have to check if the current user has permission giving the groupid. I am by no means an expert on this subject but we are in the process of implementing such a system now and it's easier (if you can) to have a limited viewing list form and then based on security let them see the edit or details form. _________________ Bob K. Real Word Processing for your Real Studio Applications with Formatted Text Control http://www.bkeeney.com/formatted-text-control/ Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 2 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
