Table Design Help

[Tansar]

Hi,
I am trying to set up some tables for my DB.
I have a list of Schools, Streets, (Council) Wards and ORDERS.

What I'm trying to achieve, is to see
which SCHOOL has an ORDER
which STREET has no ORDER
which WARD has what ORDER

The street table has some streets more than once, as there may be a same street in another Ward.

Any advice appreciate. Thank you.

 

[Dennisk]

I would create 3 tables. Here are the minimum data sets I would start off with.

tblWard
WardID autonumber
Wardname string

tblStreet
StreetID Autonumber
StreetName
StreetPostCode (many roads/streets run through post code boundaries)
WardID

tblSchool
SchoolID Autonumber
SchoolName String
StreetID

 

[Tansar]

Thank you for your reply.

What about the ORDERS table and where should I be linking this to?

I've had a play with ith earlier, see attachment.

Thanks.

 

[Dennisk]

your order table would be similar to this

tblOrders
orderID - primary Key
SchoolsID - Foreign Key
orderDate

tblOrderDetail
OrderDetailID - Primary Key
OrderID - Foreign Key
OrderDetails - could be a foreign key to a product
OrderCost