[RESOLVED] Take a look at my database design... suggestions welcome

Hey guys/girls. I just finished my FIRST real sql database. I have saved a screenshot of the database relationships diagram. I haven't started writing the program yet, but any suggestions in the database design would be appreciated before I get to the programming phase. I used smallint, smallmoney, smalldatetime, and varchar wherever possible for the fields. The ID fields are all smallint, set to be identity, increment 1, and seed 1. Is that appropriate?

The reason for all the movement fields is the items will be shuttled several times and need to be tracked for each person handling the products. The way the items will move is, store, truck driver, warehouse, driver, service shop, driver, warehouse, driver, store. It's alot of handling, but it's the way our company functions at this moment.

Here's the JPG of the relationships diagram: http://www.geocities.com/stillmidnig...ipsDiagram.jpg

Thanks!

The only thing that looks wrong to me is repeated fields in Movement and Work (driverID's/pCost's/...).

Why have you got them like that? Is there going to be 'repeated' data (not the same data, but the data could be swapped between the fields) placed in separate fields, or are they actually different things?

driverID's and dpuDates are just to track which driver picked up the item and at what time. That way someone is held accountable for where the product is. Essentially when a driver is at a location about to pick up a piece of service, they will "login" to the program with their "password" and it will then date/time stamp and show that they have possession of the item. Then at the next location the person who receives the item in will have to "login" with their "password" and it will date/time stamp again to show they've received it. There's 10 "signatures" for ever piece of service sent. Meaning it is handled 10 times. Store entry, driver,warehouse,driver,servicetech receiving,servicetech work completed, driver,warehouse,driver,store because we only go to certain stores certain days and service on other days. So the warehouse functions as a middleman for the process.

the pOrder and pCosts is in case (like computer repair for instance) the item needs more than 1 part. It will show pOrder, what was ordered, and pCosts, the cost of the item. Many times these fields may be completely blank, sometimes 1 or 2 might be used, and on rare occasion 3 or 4 parts might be needed for a machine.

I guess i was thinking the parts should be listed and not gobbled into one field. I could do, porder - cdrw,fan,ram pcost - $65.95, but then it doesn't really allow me to track what parts cost what amount. I'm also contemplating making a seperate database for parts inventory so I can create purchase orders and track inventory / serial numbers and the like, but that will have to be down the way a bit after I become more skilled and have more free time. The project already turned out to be more daunting than initially expected.

Thanks for your reply! if you think something should be changed, let me know

-wade-

Movement needs to have a COMPOUND PRIMARY KEY (two columns)...

ServiceId+dpuDate

Then only have DriverId, EmployeeId and whDate as columns.

Actually - if movement is tracking a package through drivers and tech's then you want to have only one "person" table - with both drivers and employee's in it.

With you design I cannot go to a single table - with a particular SERVICEID and actually "know" the current status of that ServiceId entity.

Hope I'm not being too critical - but you asked :)

Quote:

---

Movement needs to have a COMPOUND PRIMARY KEY (two columns)..

---

Why? I don't understand. I am linking it to the entry table where the serviceID is set when an item is entered.

Quote:

---

Then only have DriverId, EmployeeId and whDate as columns.

---

Not sure what that means. Please elaborate?

Quote:

---

Actually - if movement is tracking a package through drivers and tech's then you want to have only one "person" table - with both drivers and employee's in it.

---

Yes this would work, but I will need to know if they're a driver, employee, or service tech, I guess it'd be 2 tables, one specifying typeID, employeeType... and the other employeeID, typeID, name, password. Sound a little more economical?

Quote:

---

With you design I cannot go to a single table - with a particular SERVICEID and actually "know" the current status of that ServiceId entity.

---

I initially was going to make the entry, movement, and work tables be all one table as that information is all needed for 1 service transaction. I just organized it separately for ease of design. 1 large table was a little too daunting. I could now put them all into one, since I know what fields I need. Do you think this would be a better solution?

Quote:

---

Hope I'm not being too critical - but you asked

---

CERTAINLY NOT! I love criticism. The quickest way to get better is to learn from some of the best. I bowl regularly and I beg for criticism from people who are better than me. I steal peoples ideas and make them my own. I greatly appreciate it!

It's basically against the normal-form rules to have a repeating set of fields in a table.

So that Movement table has repeating fields for drivers and employees to and from the store...

With that design you could not write a query to find out what package a driver has today - right now - as there is not a single column to WHERE clause off of. Right?

Having all those columns in a single row might look compact - but it's actually flat. And flat is great for data-warehousing and OLAP analysis.

But if this is a transactional database - OLTP - meaning that you are going to write a UI to track, process and maintain these SERVICE entities, you should try to be more 3rd-normal-form about it.

I was confused about DriverId and EmployeeId - so my suggestion to put them as fields in MOVEMENT was poor...

Let's use this:

Quote:

---

That way someone is held accountable for where the product is.

---

as being the paramount objective.

If so then a table needs to be designed that has the "genesis" of the item - the ENTRY table seems to approach this.

Then a table needs to be designed to track the location/stage/status of the item - let's call it MOVEMENT.

I would envision that being:

serviceId - obvious value - links to ENTRY
whenDate - date/time stamp - every time someone touches/takes/becomes responsible for the item

...these two columns are taken as "one" for purpose of INDEX/PRIMARY KEY - thus a COMPOUND PRIMARY KEY - together they will always be unique

whoId - Id of person who has the item

These three fields become the "main" tracking table that says where an item is:

SELECT TOP 1 WHOID FROM MOVEMENT WHERE SERVICEID=123 ORDER BY WHENDATE DESC

gives me who has the product right now - latest entry.

SELECT WHOID, WHENDATE FROM MOVEMENT WHERE SERVICEID=123 ORDER BY WHENDATE

give me a complete tracking of that item - in date order.

There could be other columns in this table as well - maybe STATUS - but that's getting past where we are at the moment...

I *think* I know what you're saying. I'll do some re-design tonight/tomorrow and repost my new results. Thanks for the suggestions, I can see it shrinking already!

-wade-

UPDATED. Is this what you meant? I feel a little bit better about this now. I'm sure it can be tweaked a bit more. I've revised it many times, each step I have screenshot of. Let me know what you think. Thanks in advance!

and oh yeah... INDEX/PRIMARY KEY... what is "INDEX". I understand primary key... how is index any different? i haven't set ANY indexes in ANY of the tables.

phase4:
http://www.geocities.com/stillmidnig...ipDiagram4.jpg
phase3:
http://www.geocities.com/stillmidnig...ipDiagram3.jpg
phase2:
http://www.geocities.com/stillmidnig...ipDiagram2.jpg
the original:
http://www.geocities.com/stillmidnig...ipsDiagram.jpg

-wade-

That's looking much cleaner.

Explain PARTS and ITEMS to me - how many can be involved in a SERVICEID? Tell me the "real life" story of this interaction - not the DB layout that you have...

INDEX and PRIMARY KEY...

They are basically the same thing. A table can only have one PRIMARY KEY though - this is an INDEX - which means that it can be searched because it's stored in "true ascending" order - physically - on disk. A binary search (start in the middle - is the value higher or lower - now we know to look in the middle of the top or bottom half...this repeats until the key is found in the index) is used to locate keys in an index. It's highly desired that PRIMARY KEY's are unique in a table - for UPDATE reasons, for example...

A table through can have several INDEXES though - each one added after the PRIMARY KEY is basically an ALTERNATE KEY. Let's say you have a PAY CHECK TABLE - the primary key would be the CHECK NUMBER - it's unique and logically becomes the PK. But - let's say you typically want to find checks that were issued on a given date - or date range, for example. So you find you have lots of WHERE CHECKDATE='xxxx' statements in your queries. By adding an ALTERNATE KEY - a second INDEX - of the CHECKDATE, these rows are found much faster. Were refer to searches that find rows based on an INDEX as an INDEX SCAN. If there is not INDEX to satisfy a WHERE clause search then a TABLE SCAN is done. We have tables with 3, 4 and 5 million rows - so you can see how avoiding a TABLE SCAN to find rows is important to us.

ALTERNATE keys do not need to be unique - but they can be, and that's an additional feature of having them - forcing UNIQUE constraints on a particular column in a table.

Hope I'm not overloading you...

With MS SQL server you can also have "CLUSTERED PRIMARY KEYS". The DATA part of a table and the INDEX part of a table do not need to be in the same place. But with a CLUSTERED PRIMARY KEY you are telling SQL to actually store the "DATA" with the "INDEX leaves" so that you are truly, physically, ordering your DATA on DISK in the order of the PRIMARY KEY. For systems like our STUDENT INFO packages we make sure to have the STUDENT ID, for example, be first in the COMPOUND PRIMARY KEY's of our tables - and make these CLUSTERED PRIMARY KEYS. That means that all the data for a student is physically on the same spot on the disk. With disk-blocks being 8000+ bytes in MS SQL we might actually be able to get all a student data for one student from a table in a single I/O operation. Doing things like this helps the DATABASE ENGINE perform much faster.

And just to add one more point...

Back to our PAY CHECK table...

Let's say we typically want to know the amount of money paid on a given date range. If we make the ALTERNATE KEY (the one we spoke of above) be two columns - the CHECK DATE - and the AMOUNT PAID, then a QUERY for SUM(AMOUNT) using the WHERE clause on just CHECK DATE can use just the index - just the alternate key - to get the data requested. The DATA part of the table can be completely ignored because we stuck the AMOUNT PAID value into the actual index. This obviously takes more disk space - UPDATE's can be more expensive - but the value to the SELECT is great.

Whew ;)

1 item per service record. Item table will be obtaining description information off of the microsoft access database of our "rental" software we have. Every item is assigned a unique "stock number" when we receive it in at the warehouse. We then rent these items to customers, and they return them when they dont want them anymore or when they're broken. As you can imagine, customer care of products is quite lacking when they don't even own them. The software we're using is a bit lacking, and eventually I'd like to re-write the whole thing. The guy that wrote this program is probably close to a millionaire and the software is horrible, buggy,the databasing is sloppy, and it lacks alot of functionality -- service being one of them. It just has a field on a table called serviceShop and it stores where the item was sent to. Our company needs tracking as it's growing too fast.

Parts ordered I would guess a maximum would be 4 parts per serviceID/item. Usually 0 or 1 or 2 parts.

One stock number may be sent to service more than 1 time. So it may have parts ordered on different occasions, but will be assigned to a different ServiceID since it is a different time it was shuttled in to be repaired.

I believe I understand the index vs primary key now. Great explanation you wrote up with good examples. By setting a column as primary key, is that automatically making it indexed?

I have 3 concerns. 1 is the passwords table. With our company there's less than a couple hundred employees. But if the chain ever grew, the password list could be quite large. There's no way really to make that indexable and every time someone takes possession of a service item it will be logging them in by password. Another concern is the item table. Every time an item is entered into service it is going to search the stock numbers to make sure of two things. 1 that the stock number doesn't exist already to prevent duplicate entries (again another table search) and 2 ... if the stock number exists check the description and make sure there hasn't been a description update in the main rental software. When someone checks to see what parts were ordered / put into a service item, it will also be table-searching the parts table. Do you think these table searches will be a problem? OR is there some way to index them. I couldn't come up with one.

made a couple minor changes. The primary key on the movement table i changed to be just the date. i also removed employeeID from the main table.

http://www.geocities.com/stillmidnig...ipDiagram5.jpg

Thanks for all the help!