I am attempting to create a database to keep track of my customers and my sales to each customer. I currently have organized the database as shown in the image below, but I cannot create a relationship between the fields titled Ordernum in the Sales and Orders tables. I get an error that reads: Integrity constraint violation - no parent 1137905, table: Sales in statement[ALTER TABLE "Sales" ADD FOREIGN LEY ("Ordernum") REFERENCES "Orders" (Ordernum")]
Customer numbers are unique, as are order numbers. However, the relationship between customers and order numbers is a one to many relationship.
I'm not sure where my problem lies - I'm wondering if I should simplify the database, removing the Sales table and simply putting the customer number in with each order. (It's been a LONG time since Database 101... like 27+ years - so I'm trying to recall what to do about normalizing data, etc).
I'll be trying to run reports based on a weekly timeframe, and also running cumulative total reports by customer. Any suggestions would be helpful.
OpenOffice 3.1 Windows XP