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.
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.
Originally Posted by BrailleSchool
I watched a video and was told pretty much that designing the database layout is the first step.
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!).
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.
Last edited by leinad31; Mar 9th, 2009 at 06:33 AM.
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.
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.
Last edited by leinad31; Mar 9th, 2009 at 08:38 AM.
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
An obscure body in the SK system. The inhabitants call it Earth
Posts
7,957
Re: Does my first ERD look right?
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 ).
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.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Reply With Quote
Originally Posted by BrailleSchool
).