DB design review

[ChrisWisselo]

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

[NeoPa]

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.

[TheSmileyCoder]

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.

[ChrisWisselo]

@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.

[NeoPa]

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].

[mshmyob]

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,

[ChrisWisselo]

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

[NeoPa]

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.