MySQL Database design for placing service orders

[Chris Morley]

Hi, I recently posted to comp.databases but unfortunately have yet to
receive a reply. I would like to get the design cracked this weekend so im
positing here in hope of a response :)

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

[sma1king]

"Chris Morley" <g18c@nospam.hotmail.com> wrote in message
news:tZf_c.1295$Ya2.427@newsfe5-gui.ntli.net...

> Hi, I recently posted to comp.databases but unfortunately have yet to
> receive a reply. I would like to get the design cracked this weekend so im
> positing here in hope of a response :)
>
> 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
>

Chris,

DB design is not my forte, but.... how about a customer table, a service
table, and an orders table. If your service table contains fields for
maximum and minimum duration (this service only available for 1 month, this
service only available for minimum 12 months, etc.), then three tables let
you record your prices, services, duration, customers and orders. Your
customer table can key off name or number, your service key off a service
name or service id, and you can set your prices by duration unit (month,
quarter, year, etc.). This way, you only need a need a primary for each
table, and can select from the customer and order tables to generate
billings and reports. The service table is used to generate the order form
on your page (along with the customer table for repeat customers).

Customer table
cust. name Primary (no duplicates)
cust. add1
cust. add2
cust. city
cust. state
cust. zip
cust. tel
cust. number (optional, increment, could be Primary)

Service table
serv. name Primary
serv. description
serv. period
serv. min dur
serv. max dur
serv. price per period

Orders table
cust. name
order number Primary, increment
order date
order service start date (if different from order date)
order value ($) (dur & price)
order serv. name
order duration
order status (unbilled, billed, paid, overdue, cancelled, etc.)


setup - create db, create tables, enter service names, durations, pricing

generate order page - first, check if existing customer - generate same
form, but fill in customer info if existing, get any changes in customer
info that need to be made, standard form for orders - service name,
description, price per period, periods (values including and between
serv.min.dur and serv.max.dur) -

receive data, create or update customer record, create order record.

new customer - create customer record, enter customer info

new order - create order record, enter cust name, increment order number,
enter date, enter serv. name, enter duration, calc value (price * duration)

generate bills - by customer, all orders not past term and not cancelled and
not paid

generate reports - by service (pull orders that match, total revenue,
average duration, group by month, quarter, year), by customer (pull all
orders, total by customer, by service), by period (pull orders created
during period, number of customers, number of orders, average price, total
revenue), by order status (total orders within status, percentage of paid vs
overdue, etc.)

Will that do it for you?

George

[Ninjaboy]

I'm sure you already aware of this. but just in case.
MySQL - Default structure MyISAM does not support foreign keys. You
would want to use InnoDB engine instead. InnoDB supports transactions with
Cascade UPdated / Delete on foreing keys.



"sma1king" <gking@geking.com> wrote in message
news:S2o_c.987$x12.551@trnddc05...

> "Chris Morley" <g18c@nospam.hotmail.com> wrote in message
> news:tZf_c.1295$Ya2.427@newsfe5-gui.ntli.net...

>> Hi, I recently posted to comp.databases but unfortunately have yet to
>> receive a reply. I would like to get the design cracked this weekend so
>> im
>> positing here in hope of a response :)
>>
>> 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
>>

> Chris,
>
> DB design is not my forte, but.... how about a customer table, a service
> table, and an orders table. If your service table contains fields for
> maximum and minimum duration (this service only available for 1 month,
> this
> service only available for minimum 12 months, etc.), then three tables let
> you record your prices, services, duration, customers and orders. Your
> customer table can key off name or number, your service key off a service
> name or service id, and you can set your prices by duration unit (month,
> quarter, year, etc.). This way, you only need a need a primary for each
> table, and can select from the customer and order tables to generate
> billings and reports. The service table is used to generate the order
> form
> on your page (along with the customer table for repeat customers).
>
> Customer table
> cust. name Primary (no duplicates)
> cust. add1
> cust. add2
> cust. city
> cust. state
> cust. zip
> cust. tel
> cust. number (optional, increment, could be Primary)
>
> Service table
> serv. name Primary
> serv. description
> serv. period
> serv. min dur
> serv. max dur
> serv. price per period
>
> Orders table
> cust. name
> order number Primary, increment
> order date
> order service start date (if different from order date)
> order value ($) (dur & price)
> order serv. name
> order duration
> order status (unbilled, billed, paid, overdue, cancelled, etc.)
>
>
> setup - create db, create tables, enter service names, durations, pricing
>
> generate order page - first, check if existing customer - generate same
> form, but fill in customer info if existing, get any changes in customer
> info that need to be made, standard form for orders - service name,
> description, price per period, periods (values including and between
> serv.min.dur and serv.max.dur) -
>
> receive data, create or update customer record, create order record.
>
> new customer - create customer record, enter customer info
>
> new order - create order record, enter cust name, increment order number,
> enter date, enter serv. name, enter duration, calc value (price *
> duration)
>
> generate bills - by customer, all orders not past term and not cancelled
> and
> not paid
>
> generate reports - by service (pull orders that match, total revenue,
> average duration, group by month, quarter, year), by customer (pull all
> orders, total by customer, by service), by period (pull orders created
> during period, number of customers, number of orders, average price, total
> revenue), by order status (total orders within status, percentage of paid
> vs
> overdue, etc.)
>
> Will that do it for you?
>
> George
>
>