Printable View
Hi all,
I am planning my db layout for the first time using an ERD. I have never done an ERD before and would like to know if you think my layout is optimized or if you have any suggestions in regard to improving it.
I have not done much coding on the app, so wont have much to change in regard to VB code. I watched a video and was told pretty much that designing the database layout is the first step.
Thanks for any ideas/comments/suggestions
In terms of the way you've laid it out there is room for improvement, for example:
- As Users links to lots of others, I would have put it in the centre - that way the links can be shorter, and easier to spot.
- I would have put Users_Email off to the edge (and Type_Email above it) so that they don't "merge" into the rest of it.
Similar applies to others such as CompDepts and Type_Address- The arrows for the links are much easier to understand if they start/end on the fields they indicate a link for.
(as it is, I needed to search for them each time, and got bored of doing it!).
In terms of the actual data, the only thing that jumps out at me at the moment is User_Permissions.
I presume the *Menu fields are to indicate which parts of the program they can use, and the *Access fields to indicate what actions they can do... but do those actions really apply to all parts of the program? If not (or you think they might not at some point in the future), it would be better to split it into two or more tables.
Also, if you think there will be multiple users with identical permissions, it would be better to assign users to Groups, and have the permissions assigned to the Groups rather than individuals - that way there is far less effort to add/remove users of makes changes to permissions for a group of people.
Absolutely - the program is just an interface to the DB, so you want to make sure the DB is ready first (otherwise you'll need to keep on re-designing/re-writing parts of the program to suit it!).Quote:
Originally Posted by BrailleSchool
thanks for the comments si. very much appreciated. looks like i have a lot of work to do and definately will be taking on your suggestions.
ill definately 'group' the items in the ERD for easier analysis next time
Everything looks ok since the database transactions that will fill up those tables are pretty straightforward and will complete almost instantaneously... just be aware that "exceptions" to the normalization concepts typically taught are possible and often involve long complex transactions with table locking and serialized control/reference numbers as keys.
Consider an insurance policy table where the policy number is the PK and is serialized (can't create them in parallel, 2nd transaction must wait for preceding transaction to finish since if rollback occurs then 2nd transactions will use policy number that would have been used had first transaction completed successfully). And there are numerous child tables (with their own children) wherin referential integrity constraint starts with on policy number and extends to nth records in nth tables.
Bottleneck in preceding data architecture then becomes evident since serialized PK is generated first but not committed immediately. Until serialized PK is committed (or entire transaction which also fills child tables with data are committed), numerous table locks are put into effect (the FKs on the serialized PK and cascaded referential integrity constraints). Deadlocks can occur if transactions from other sessions already have locks on some of the tables and all of them are waiting on each other to release locks.
Sample transaction would have been better off with an additional surrogate key (auto-increment or GUID internal key) for referential integrity so records can be committed immediately rather than relying on just the serialized natural key policy number. Generation of serialized policy number can then become the last step in the transaction and the numerous locks can be avoided... if unsuccessful then existing master record is just tagged accordingly as spoiled/invalid (no policy number) for immediate deletion or scheduled purging later on.
Concurrency problems are minimized using two keys rather than the single key often demonstrated in normalization samples. Again just be aware of special considerations for long complex transactions since projects involving serialized control numbers that are audited and must not have gaps do occur.
Looking at the dbo.NextOfKin and dbo.Addresses tables I would remove the NextofKin FK2 from the dbo.Addresses table and have an AddressID FK inserted into the dbo.NextOfKin table instead.
This would then make it similar to the way the dbo.User_Profile table uses the AddressID, as it has an AddressID in it as a foreign key.
Personnaly I would remove the NextofKin from User_Profile and create a join table between User_Profiel and NextOfKin with FKs to both tables.
Many ways to approach this... if this were a hybrid database (relational and xml db in one), I would consider using XML to store some of the info to simplify the table design. Attached document is old but you can review the concepts discussed.
thanks all for the insight and suggestions. so much to take in but should be a good learning process. i want to make sure i can cover as many bases as possible and make data integrity as perfect as possible. will definately do more research and see how it goes. looks like i have a lot to do here :)
Just a couple of things.
If I'm really pedantic an ERD doesn't contain fields, it just shows the relationships between tables. In reality I think most people would agree that a diagram that shows all the field names is more useful but if you're producing this for an academic project you can get marked down for that (and I was, once :mad: ).
Another good idea is to always make your crows feet point down (or in your case your arrows point up). The one exception being a table that references itselfThis has 2 benefits: 1. It makes the diagram much clearer and 2. It immediately exposes any circular references.
Other than that it's an extremely good first effort.