Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Comments on this database design??

Posted on 2004-11-17
13
Medium Priority
?
354 Views
Last Modified: 2013-12-03
hello experts,

what are your views on this database design based on normalisation, flexibility, and complexity??

http://www5.domaindlx.com/infosys/databasedesign.jpg

i created it and would like to have youur views..

0
Comment
Question by:SniperVishal
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 17

Assisted Solution

by:BillAn1
BillAn1 earned 240 total points
ID: 12603569
In general, your schema looks fine, and it would really depend on your particular application whether it fits perfectly or not.
As you have invited comment, I will give opinion on a few points, none of which you need adpot, depending on your particular circumstances etc.
1. More a style guide than anything else, it is usually recommended to keep all your entities in the singular, i.e. call your Table Sale, not Sales, since each row in the table is a single sale, not a group of sales (as you ahve done for Employee, Customer etc)
2. Customer and Employee are similar objects / entities. You may benefit from rolling them into one entity? Or perhaps creating an entitiy called person (personID, firstName, LastName, Address1, Address2, Phone, Email) and then normailising the personal details out of the two entities Customer & Employee. The benefits here are that you could then have the same person as both an employee and a customer. Also, for maintainability, if you want to change the way you want to record addresses, you only have one table to update rather than 2 etc. You may even want to normalise further and create an entity called Address which could be shared by both Person and Supplier. The value of all this will depend on your application, whether or not there is any benefit on having a standard form for address etc, and whether or not there is any benefit in normalising addresses (e.g. if multiple people from the same address - would it benefit to handle this as a single address or not? - depends on whether you ever want to track the fact that people live at the same address, or whether you just want to treat them as seperate addresses...)
3. You do have one potential pitfall - you have an entity called PurchaseOrderDetails which has FKs to both Product and Supplier. The problem is that you may well record here an invalid record, since the combination of Product & Supplier may not be a valid one, as stipulated in the Supplies table. You might consider changing the foregin keys from pointing to the individual tables to a single (composite) FK which points to the Supplies table instead.
4. You might want to normalise out vat. As VAT tends to be applied in groups, where the rate for a group changes.
5. Be careful with aggregated data. In Your Sales table you ahve colums such as TotAmt etc, which can be derived from the individual SalesDetails records. Strictly, you should not store this data, as it is not normalised. Although, for practical purposes, it is often benefical to do so. You might consider having triggers on the SalesDetails table to ensure that these aggregated values are automatically kept in sync.
6. In your Brand table you have a field called BCountry. Does this mean that in your model a brand is specific to a singel country? Therefore, is it possible that a product may be braded differentyl in different coutries? If so you may need a many-to-many relationship between Product and Brand?
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 12603600
Looks OK.
one comment,
You have it set up that a product can be supplied by many suppliers.  Is this really true?
if so I would have thought that whn you sell a product (SalesDetails) will you not have to indicate which supplier is going to supply the product? you have it set for the purchase order details

/Richard

0
 
LVL 7

Expert Comment

by:Navicerts
ID: 12605336
Hello,

One thing i was thinking while looking at it is maybe you want to segrigate username and password into a seprate table so that you can key them.  the way it is now two diffrent employee's could have the same username and diffrent passwords, not sure how that would work :)

-Navicerts
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 7

Expert Comment

by:Navicerts
ID: 12605368
Also looking at three fields in your PurchaseOrders table I am wondering if one of them is a calculated field or not?

I see DateOrdered, DateRequired, and DateRequested.  I don't know if it is the case or not but i thought that some companies might have the "date required" be simply the date it was promised - 2 buisness days or something.  Once again i dont know if this is the way your company works but it was just a thought.

-Navicerts
0
 
LVL 7

Accepted Solution

by:
Navicerts earned 360 total points
ID: 12605593
I am not sure what the common naming convention is but one thing i noticed about some fields was that they adopted their table description into the field name.  For instance, on your "Brand" table your fields are "BName" and "BCountry" but on your employee table you do not say "ELastName" and "EFirstName".  I am wondering if there might be a more consistent way of doing this.

Also, I am assuming that if an employee or a customer were to change their address this would be done by modifying the information that is already in that record.  The downfall to this is you will loose the persons previous address and queries such as “Where were our customers coming from in 1998” will be invalid.  On the other hand if you create a new employee id or customer id if their address changes you can imagine that it will have implications as well.  

In your Supplier table you only have room for one contact for each supplier.  If you were to have information of two contacts for the supplier or one of your contacts were to leave you would loose information about one of them or you would have to create an additional record and Supplier ID causing confusion.

On table SalesDetails I am not sure what the field “Amount” is you could rename that to price maybe?

It looks like a good start and perhaps you have specific needs that speak to all the things that I listed.  Starting is the most difficult part I found when designing a DB, I am working on one too!  

Good Luck!

-Navicerts
0
 

Author Comment

by:SniperVishal
ID: 12605961
The comments are very useful... Great job..

By the way the database design is for a warehouse.

I further have some probs:
i need the tables to be in 3rd normal form..

I am getting a confusion:
i think there are partial dependencies on the table PurchaseOrdersDetails as, i think QTY depends on ProductID which is part of a Composite primary key.

the same probs for SalesDetails...

The main issue is normalisation..



0
 

Author Comment

by:SniperVishal
ID: 12606486
How can the transitive dependencies be solved?
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12608051
"i think there are partial dependencies on the table PurchaseOrdersDetails"
I don't think so - if QTY only depended on ProductID, and not also on OrderID that could tell how many were ordered, just by knowing the product, and not knowing which order it actaully wasy (i.e. that all orders for a product were for the saem quantitiy), which I presume is not the case in your world. If it is true (which  it may be - if there is a rule that people must always order the same quantity, and if they require more, they must place a new order....) that the QTY ordered depends only on the product, then remove QTY from this table, and put it in the Product Table.
The only problem with your PurchaseOrderDetails table is that the relationship should be between PurchaseOrderDetails and Supplies, not to Supplier & Product sepertately.

"How can the transitive dependencies be solved?" nobody in the real world ever asks such a question. Can I assume that this is homework related? From your model there is no obvious transitive dependencies, but this is not always evident from the model itself, but may be deduced from the buisness rules involved etc. We are not in a position to do homework for you, nor to provide general training in database theory, but rather to answer specific problems etc. If you have a specific problem regarding normalisation that you need help with, detail the issue and we can give further assistance.
0
 
LVL 7

Expert Comment

by:Navicerts
ID: 12608147
"How can the transitive dependencies be solved?"

Like BillAn1 mentioned i really don't view things in that way.  Hmm, i suppose this would be like the math property where A > B and B > C therefore A > C.  

All your relations seem fairly direct, perhaps it is best to think of your DB in a more "common sense" type of approach.

Sorry i can't help you much more on that one.

-Navicerts
0
 

Author Comment

by:SniperVishal
ID: 12611596
Not a hw question.. i am learning to create databases in sql server.. i want to create nice normalised tables..

i corrected some of the errors i made.
0
 
LVL 7

Expert Comment

by:Navicerts
ID: 12615846
I think one of the main things to keep in mind when you are trying to create a normalized DB is will you be able to change the way you do buisness without changing the DB signifigantly?

For instance...

Currently on the DB i am working on they weighed animals twice in thier life, for this they had a table with the following (along with sevral other columns but ill keep it simple).

Animal ID
Weight One
Weight Two

Now they way that is set up now (NOT normalized) if they wanted to weight animals three times in thier life it would require a signifigant change in the system so i changed it to the following...

Animal ID
Weight
Date

This way they can weight the animals as many times as they want throughout its life with no change to the DB.  I think these are some of the things you need to keep in mind.

-Will they be able to change the way they do things (is my DB robust)
-Will they be able to ask all sorts of questions of your DB, find out what they want from it first and make sure they can get all this information
-Can you add new things into your DB without having a lot of hardships
-Will your data remain valid, good integrity checking!!

Good Luck

-Navicerts

0
 

Author Comment

by:SniperVishal
ID: 12623515
Ya its regarding flexibility...

i think there are too many assumptions that have to be made...
or rather study the system deeply..

i think flexibility is the hardest part in database design.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question