Comments on this database design??

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

SniperVishalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BillAn1Commented:
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
RichardCorrieCommented:
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
NavicertsCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NavicertsCommented:
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
NavicertsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SniperVishalAuthor Commented:
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
SniperVishalAuthor Commented:
How can the transitive dependencies be solved?
0
BillAn1Commented:
"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
NavicertsCommented:
"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
SniperVishalAuthor Commented:
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
NavicertsCommented:
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
SniperVishalAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Fonts Typography

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.