Hi, I am currently designing a simple service orders database. I have played
around with MySQL a bit but this is the first time I'm using it in anger, I
have a few design queries to make sure I am implementing the database
correctly.
Currently I have a number of tables, the first being the package table
(lists the different service names - such as small, medium etc). Note in the
tables I have trimmed a lot of the unnecessary stuff to save space:
Package Table:
packageID
name
PRIMARY KEY (packageID)
The package durations table lists the different duartions of packages in
months, that the customer can select. I don't want duplicate durations for a
particular package, so I make unique primary key based on the packageID and
duration:
Package Durations Table:
packageID
duration
PRIMARY KEY (packageID, duartion)
Now when customers place an order they will select a package, and will need
to select a duartion. What's the best way to do this? Either I can have
packageID and a duration in the orders table, or I can actually give the
package duartions table a new primary key which is unique to define a
certain 'package/duration' combination. What's the best way to do this? The
second way would mean I would only need one field in the orders table to
store the package duration option. Not sure if this is ok, so I have just
listed the orders table as I have currently implemented it.
Orders Table:
orderID
customerID
packageID
duration
PRIMARY KEY (orderID)
I have been using fabFORCE dbdesigner 4 (is excellent). However I have a
query with regard to when I create relationships in the database editor, it
creates a lot of primary keys (unless using non-identifying relationships -
haven't come across these before from my book reading). Using the above
orders table as an example, it would make all the fields listed there
primary keys. Is this ok? When I was designing the database by hand I
created only the orderID as a primary key, and set the customerID, packageID
and duration as normal fields... not sure if this is design is correct but
it did work ok. Is it the case of the more primary keys the better so the
database performance can be increased?
I have also used foreign keys if the value is derived from another table's
primary key (in the orders table above, I would set customerID, packageID as
foreign). I presume MySQL performs some form of optimisation or hashing if
foreign keys are present in tables? Again, am I using this feature
correctly?
If anyone could give me some pointers would be very much appreciated.
TIA
Chris
