I have been trying to implement a database for an assignment. the problem comes in that we have been given a spec and told to design ELH etc...the tutor has looked at a particular ELh and given it the green light to implement. my question is to me it doesnt look right..any clues as to whether my design is correct or the supposed correct design or if their both wrong...etc..as u can see i'm a newbie here!!.so treat me gently.

secenario is a shoe shop ordering database, where suppliers supply many shoes but each shoe style is only made by 1 supplier.

supposed correct design:

Purchase_No, (PK)

Date,

Item Code,

Value,

Supplier ID.



Purchase_No. (PK/FK),

Stock_ID (PK/FK)

Size,

Quantity,

Line Value,



Supplier_ID, (PK)

Shoe Type,

Item Code,

Price,

Supplier Name,

Location





Stock_ID, (PK)

Price,

Supplier ID,

Size,

Stock Level,

Description



links are 1:N supplier -purchases table

1:N stock to purchase line

M:1 purchase line to purchaes



MY design is as follows:



SupplierID (PK)

Name

Address



ShoeID (PK)

Description

Size

UnitCost

StockLevel



PurchaseNo (PK)

Date

TotalCost



PurchaseID (PK)

ShoeID(PK)

Quantity



IndividualShoe(PK)

SupplierID

ShoeID



tables linked as follows



purchases 1:N PurchaseLine

PurchaseLine M:1 individual shoe

Individulalshoe M:1 Supplier

IndividualShoe M:1 Shoe



Hope i'm not confusing you all...too much!



thanks in advance for any help

The "style" constraint is the thing that is probably most confusing.

I am curious as why you have two purchasing tables?

I believe you could use only one purchasing table containing all the purchase information connected with a CUSTOMERID. That is another element missing in your schema is who and where is the customer situtated in your scenario?

Who is the supplier ultimately selling their product to?

Or is this scenario simply a "stock" ordering system where the supplier orders the shoes?

Probably raised the level of confusion a tad, but in order to provide the best advice, these missing gaps need to be addressed.
========================================
Buh Bye!

Cheers,
Me

I noticed that i had a table over in my design and could get away with just the purchase order table.

The system itself is just an ordering system with no customer involovement.

My question is still really is the 1st set of tables correct or is my design, i think it's my design but i don't really know otherwise i wouldn't be asking...

Thanks for the input, much apreciated.

First things first, there is really no "correct" design when modeling databases. The reality is while your instructor has probably told you to try to design database schemas to 3NF or 4NF if at all possible, the problem is that in the "real" world of applying interfaces and applications to the database, sometimes less normalized databases function much better. The problem is that you are working with a hypothetical example, but in the real world, you would have to make adjustments to make the database work efficiently.

My opinion is that the first set of tables are more sensible and logical than your design. The relationships are better to pinpoint in the first set than your design. I think your design is too complex for addressing the functional requirements.
========================================
Buh Bye!

Cheers,
Me