472,353 Members | 1,064 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

DB design review

Hello everyone.

I have designed a database, but would like to get some feedback before actually building it. At this time I'm particular curious about the fact if it matches normalisation and if so, which class (1NF, 2NF or 3NF). Let's find out if I understand the normalisation-principle. LOL

The whole design is rather big (15 tables), but I would like to gather/ store as much info as possible on customers, products and customer's sites.

Any comments/ suggestions/ ideas on functionality are most welcome. The design is viewable as a .png-image, so kind request to the mod's to make it visible?

Regards,
Chris

Attached Images
File Type: jpg DB Design.jpg (76.6 KB, 15607 views)
Jan 30 '12 #1
7 10624
NeoPa
32,511 Expert Mod 16PB
Comments :
  1. It is rare (but not unheard of) for there to be a requirement for 1 to 1 links between tables. Question each that you have carefully.
  2. Contact-log doesn't fit. It cannot have a 1 to 1 with Customers and also have a 1 to Many with ContactLogDetail.
  3. Shipping appears to be linked to Orders by a 1 to 1. I would guess Shipping is a lookup table, in which case it should be a 1 to Many.
  4. PayMethod. What service does it provide?
  5. Why would a site have many detail records?
  6. Why would a product have many detail records?
  7. Why would a zone have many detail records?
  8. Decide whether a table name describes the record or the table. You have a ContactLogDetail table, whereas you also have a Customers table.
  9. Column is spelled C-o-l-u-m-n.

Just a few ideas/questions to be getting along with. Nicely laid out. Bear in mind though, that pictures on here are often of too low quality to be easily legible. I increased magnification and luckily didn't need to recognise much of the detail.
Jan 30 '12 #2
TheSmileyCoder
2,322 Expert Mod 2GB
This is not a comprehensive review, I only spent 2 minutes just trying to spot anything obvious.

If we look at the Customer<->Site<->SiteDetail tables and relations, it would probably recomend combining SiteDetail and Site into 1 tabel Site, since I dont see 1 site having more details. I would then remove SiteID from the customers table, and instead have a CustomerID in the site table, and have a (1) customer-(m) sites relation.

The intermediate tables you have, Site being one of them, seems to look like the tables I use when I have to create a many-many relationsship, and maybe thats where you got wrong. Off course you could have perfectly valid reasons for doing so, but I can't see that from here.
Jan 30 '12 #3
@NeoPa
1. Would a 1-M relationship be suitable for those tables?
2. If I change Customers - ContactLog into a 1-M, would that work?
3. Changed into 1-M
4. PayMethod provides the different Methods available for paying: [Cash, CreditCard, Check, Transfer/ Deposit, PayPal]. The details of those methods are stored in PayMethodDetail.
5. The site itself doesn't have to many details.
However, it is not unheard of that a client has several sites that are under contract (think of a hotel-chain). Therefor I reasoned to have this set-up, to keep the structure clear and managable.
6. The many detail-records on products helps to provide more detailed information to customers and also serves as a glossary for myself.
7. The zone itself doesn't have to many details.
However, many projects are divided into several zones. Therefor I reasoned this set-up, to keep the structure clear and managable. Number of zones can easily go up to 10+.
8. Customers and ContactLogDetails are separated because ContactLog is a look-up table using many ContactLogDetails.
9. Corrected. :)

@TheSmileyCoder
I reviewed your remark about combining the Site and SiteDetail tables and combined those into 1 and made the addition CustomerID in it. Also removed SiteID from the customers table and made a 1-M relationship between tabel Customers and Site.



I've updated the design, so feel free to have a look and share your thoughts (@Mod's: Please enable :)).

Can one of you also tell me if this design complies with normalization and if so, which one? 1NF, 2NF or 3NF.


Attached Images
File Type: jpg DB Design_v2.jpg (74.0 KB, 5328 views)
File Type: jpg DB Design_relations.jpg (69.6 KB, 7415 views)
Jan 31 '12 #4
NeoPa
32,511 Expert Mod 16PB
I will try to deal appropriately with each response :
  1. Not necessarily. Largely not, but each needs to be assessed in its own right, depending on what you want from it. Some of these tables may not be required at all possibly.
  2. Not really. Not without changing other things. It serves no purpose. It is either a part of [Customers] or a part of [ContactLogDetail]. It's essentially redundent
  3. 8-)
  4. That answer is no real answer. You're just trotting out your original reasoning without thinking critically (Develop those critical thinking muscles as you'll need them big-time working in databases). If it's a 1 to 1 with [Payment] then what does it add? How would it make sense to have separate [PayMethod] and [PayMethodDetail]s?
  5. That explains why you'd have [Site], not why you'd have it separately from [SiteDetail].
  6. No. That just doesn't make sense (and doesn't really answer the question either). More critical thinking required here.
  7. See response to answer #5 above.
  8. A table name either reflects the record (IE. [Customer]. A Customer record.) or the table as a container (IE. [Customers]. A table of Customers.) All tables are containers no matter what the information contained therein is used for. Consistency is not something it is wise to do without in a database system. The bigger and more complex it gets, the more important it will be. Without it, one day (and often more than one) you will be misled into making a mistake due to these inconsistencies.
  9. 8-)

Chris, you clearly have some basic intelligence, and show an inclination to get this right. The longer into the project that it takes you to discover any problems, the more trouble they will cause you. The increase tends to be exponential. Don't answer defensively. Give serious thought to any questions posed, and only then respond explaining your original reasoning if that still makes sense to you.

The chances are you will still have missed something (We do this quite a lot after all), but that thinking alone will help you develop your understanding and allow you to progress. Database design takes thinking that generally takes some getting used to. It uses different aspects of the brain than most are accustomed to exercising. Nevertheless, as indicated before, getting this part right is so absolutely critical to the project as a whole. You're doing absolutely the right thing by asking and looking at feedback. Don't let yourself down by side-stepping the responses.

If I sound like I'm having a dig at you, then please understand that is not what's going on here. I want to encourage you to develop your good idea further - even though much of what is being said is more confusing to you at this stage than it will be further down the line when you've built up some experience of your own.

PS. Your Visio diagrams are probably clearer than the Relationship ones.
PPS. See BB Code for details on how to display your pictures yourself. The tags you're looking for are [imgnothumb] [/imgnothumb].
Jan 31 '12 #5
mshmyob
904 Expert 512MB
Hello Chris,

I am having trouble reading the graphics but you do have some more work to do as pointed out.

The table "Payment" looks to me that it has repeating groups (not recommended).

Take the 3 tables "Site, Zone, and ZoneDetail"... This looks to me that you are trying to say that:

1. Each site can have many zones
2. Each zone can have many sites

This obviously creates M:M relationship and requires a brdge table. This is what it appears you have tried to do but I notice that your PK in the table "Site" is SiteID (I assume) but it is not linked into the bridge table. Remember that the minimum numbers of fields required in a bridge table would be the PK's from the related tables (ie: SiteID and ZoneID) you can add more but these fields create the relationship for the bridge.

Maybe you could explain what the entire database is supposed to accomplish and then explain to us what each table represents (leave out bridge tables). And if you can tell us the business rules between each set of related tables as I guessed above for Site and Zone.

If you do not know two business rules (bi-directional rules) for each set of related tables then it is impossible to determine the relationsgip type between the two related tables.

cheers,
Jan 31 '12 #6
Okay, writing the reply is gonna take some time. Bare with me.

In the meanwhile: Is there a article on Bytes on how to determine (and set-up) the best kind of relationship between tables? I've been playing around, trying to change the type of one from 1-M into 1-1 or into M-M, but couldn't find any option (Access 2003).

Regards,
Chris
Jan 31 '12 #7
NeoPa
32,511 Expert Mod 16PB
Most such changes are handled by double-clicking on the link between the tables and changing the settings therein. This is not true for Many to Many links though, as these are implemented by a separate table which has Many to One links to each of the original tables.
Feb 1 '12 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Arvie | last post by:
I need some advice guys.. I am proposing that we get someone to do a complete audit/review of our Java application codebase, about 1000...
2
by: Dave Patton | last post by:
I'd appreciate any feedback on http://www.elac.bc.ca/ particularly in regards to how the pages are marked up. The markup is valid HTML 4.01...
3
by: ma740988 | last post by:
Faced with the need to instrument/log data I've decided to create an instrumentation/log class. My initial thoughts (baby steps) on this...
2
by: G.E.M.P | last post by:
High Level Session Handling Design for a Shopping cart 0) What am I missing? 1) How does OSCommerce do it? I'm thinking about building a...
21
by: Johan Tibell | last post by:
I would be grateful if someone had a minute or two to review my hash table implementation. It's not yet commented but hopefully it's short and...
10
by: vital | last post by:
Hi, I am designing the middle tier of a project. It has 6 classes and microsoft application data access block. The six classes are DBServices,...
4
maxx233
by: maxx233 | last post by:
Hello all, I'm new to OO design and have a question regarding where I should place some code. Here's a simplified situation: I'm making an app...
37
by: Phlip | last post by:
1230987za wrote: Kanze is a classically-trained "unit tester". In some circles "unit" is a QA concept - specifically, if a test fails, you only...
3
by: AnthonyT | last post by:
Hi All I have a major problem with an access query and I am near the end of my tether! I have taken over a project with a badly built access...
76
by: lorlarz | last post by:
Crockford's JavaScript, The Good Parts (a book review). This shall perhaps be the world's shortest book review (for one of the world's shortests...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.