472,364 Members | 1,870 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,364 software developers and data experts.

MySQL Database design for placing service orders

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

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:

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

Package Durations Table:
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:

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

If anyone could give me some pointers would be very much appreciated.


Jul 20 '05 #1
0 1417

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Jay Moore | last post by:
Greetings, all! I have a project for work, and I'm not sure how to efficiently do what I need to do. I'm hoping someone out there can help. Project is this: I'm creating a web-based...
by: David | last post by:
Hi, I have a MySQL database and in the front-end software I am using to Run MySQL, I have created a query on the tables which runs in the front-end, similar to the MS Access query grid. I now...
by: Chris | last post by:
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...
by: Danny J. Lesandrini | last post by:
I know this has been discussed before, as I've seen the Google posts, but they all leave me with an unanswered question: Does my DAO code executed in the front end cause the back end to bloat?...
by: RibGnaw | last post by:
Long, long ago when Access 97 first hit the streets a sample database called Northwind was shipped with it. At that time a number of articles had been posted on the correct procedure to remove all...
by: alf | last post by:
Hi, is it possible that due to OS crash or mysql itself crash or some e.g. SCSI failure to lose all the data stored in the table (let's say million of 1KB rows). In other words what is the worst...
by: dino d. | last post by:
hi everyone- my subject pretty much says it all- is there a secure way to do this? the non-secure ways are, as i understand it, to populate a listbox with indices as names, or maybe use a hidden...
by: jaad | last post by:
I have built a Database to take care of everything a rental building requires: work orders, purchase orders, task scheduling and so on… it work great but wanted to make it more efficient. Here are...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.