July 29, 2010 at 12:19 pm
I have been asked by my boss to create a transactions table for given peices of equipment, and I'm not confident in the design and so I am looking for advice.
Their would be 4 main tables
EquipmentType
Equipment
TransactionType
Transactions
At the top of the Hiearchy is the equipment type which is just a list of equipment types and associated ID's for each. The Equipment table would be where the user would create equipment and give it a name and some other specs. The transactions table, the one I'm having issues with, logs different transaction types for each piece of equipment.
The transaction table has the following fields as it sits:
TransactionID
TransTypeID
Comments
Results
TransDate
DatePeriodID
EmployeeID
ResultTypeID
All of the ID fields are foreign keys, but they are not neccesarily all filled. For example, there are instances where ResultID and EmployeeID are going to be NULL, I.e. for a transaction of type "Assignment" where we are only interested in logging when a peice of equipment goes out.
I can still create a foriegn key relation on this field that allows nulls, but is this viewed as ok database design?
August 3, 2010 at 1:23 pm
What are examples of transactions in your system? I'm imagining that an example of equipment type would be 'automobile' and that 'the blue Honda Accord with VIN 2H3...' is a piece of equipment. What is an example of a transaction and how does that related to equipment? Does a transaction use 1 piece of equipment? Could it use multiple pieces of equipment?
August 5, 2010 at 5:58 am
You can absolutely have <null> values for a FK field, so your example of that is ok.
Also, you say that these tables are the "main" tables:
EquipmentType
Equipment
TransactionType
Transactions
To me the "xxxType" tables are "lookup" tables.
ResultTypeID?
I'm not sure if this is from a FK table of possible ResultTypes?
What is the "Results" column? Is this related to ResultTypes? Can a Transactions have many results, or 1 and only 1? If so, you may want to create another table called Results:
ResultsID (PK)
TransactionID (FK from Transactions table)
Results (think this might be a description column?)
It's tough not knowing what your rules are.
Steve
August 5, 2010 at 10:09 am
robertfolkerts (8/3/2010)
1.)What are examples of transactions in your system? 2.) I'm imagining that an example of equipment type would be 'automobile' and that 'the blue Honda Accord with VIN 2H3...' is a piece of equipment. What is an example of a transaction and how does that related to equipment? Does a transaction use 1 piece of equipment? Could it use multiple pieces of equipment?
Sorry, I didn't notice how vague this description was until reading your responses.
1.) Transactions can be mutiple things, and some are specific to all equipment types, some are specific to just a single equipment type. You would have a transaction of 'Issue' which just fills the date field of the Transactions table, along with transTypeID and TransactionID, this could be used for all types of equipment. Similarily, you could have a transaction for 'Return'.
-I have gone a step further now and created a junction table that relates each type of transaction to a particular equipment type.
2.) As for what the actual entities are, they are measuring devices for recording radiation levels. So you would have a type of PAD (Personal Alpha Dosimeter) and you could create a bunch of PADs based on their serial number (ex C0205) in the equipment table. Then you assign transactions to it like Issue/Return/AssignPerson, here AssignPerson would use the employeeID field with is a FK to another table, and for transactions of type Issue/Return it would be null.
So, I answered your first questions earlier, but Transactions ARE re-usable, i.e. used for many pieces of equipment.
Steve-3_5_7_9(8/5/2010)
1.) Also, you say that these tables are the "main" tables:[...]
To me the "xxxType" tables are "lookup" tables.
2.) ResultTypeID?
I'm not sure if this is from a FK table of possible ResultTypes?
3.) What is the "Results" column? Is this related to ResultTypes? Can a Transactions have many results, or 1 and only 1? If so, you may want to create another table called Results:
ResultsID (PK)
TransactionID (FK from Transactions table)
Results (think this might be a description column?)
1.) Sorry, I meant they are the main tables in this part of the Database. The database stores much more info about people/hours worked/exposure to contaminants/etc.
2.) Result type has now been moved to a junction table, it links result style transactions to a primary key of another table. Its a weird setup, but the goal of these transactions is to assemble a record for another table. So a some point I have a stored procedure which looks through the transactions table for all relavent transactions specific to a piece of equipment, and creates a record out of those transactions which contains an exposure value, then loads that created record into another table.
3.) The results column is simple a float field which can be stored for each transaction, sometimes its used, sometimes its not. As I have explained above, the result type is now moved to a junction table that links transactions to resultTypeID. So yes, now it can correspond to many ResultTypeID, but based on the transaction via a junction table.
In the example of equipment type 'PAD', I would look for equipment C0205, find its 'Issue' / 'Return' / 'AssignPerson' transactions, as well as its measured results, say 'GammaResult' and 'AlphaResult' and create a record for each of these results so they can be loaded into another table. The reason this isnt all just loaded in the Results table from the start, is so we can store more things about the equipment that doesnt ever need to be stored with the result, like tracking info, and secondary readings not used for exposures.
Working with this example above, I would create 2 records for PAD - C0205:
<ReadingType-Gamma-ID> <GammaValue> <AssignDate> <ReturnDate> <Employee-ID>
<ReadingType-Alpha-ID> <AlphaValue> <AssignDate> <ReturnDate> <Employee-ID>
Let me know if you need better clarification. This thing has to be in use in 3 weeks, so I think this is the way Its going to work, but I'm not sure if its all that good in terms of best design practise. I guess the person heading the project is under the assumption that databases should contain as few tables as possible (2 was his words) so you don't have to look all over the database in a million tables to find stuff.
The way I see it is if it doesn't belong in a table, it shouldn't be there. Joins and indexes on joined fields are what make a database run smoothly. simply having 2 larger table would require many table scans for queries, as well it makes data entry on the frontend side hell. Everything would have to be hard coded into the frontend, in terms of what fields to fill, and what Ids to associate to new records.
I guess we kinda bump heads a lot about this sorta thing, I mean I've only be working with SQL since early this year, but from what I read, it seems most people lean away from 'generic' style tables and packing everything into one or two tables. But this is a side note/rank.
August 6, 2010 at 8:00 am
Working with this example above, I would create 2 records for PAD - C0205:
<ReadingType-Gamma-ID> <GammaValue> <AssignDate> <ReturnDate> <Employee-ID>
<ReadingType-Alpha-ID> <AlphaValue> <AssignDate> <ReturnDate> <Employee-ID>
Let me know if you need better clarification. This thing has to be in use in 3 weeks, so I think this is the way Its going to work, but I'm not sure if its all that good in terms of best design practise. I guess the person heading the project is under the assumption that databases should contain as few tables as possible (2 was his words) so you don't have to look all over the database in a million tables to find stuff.
The way I see it is if it doesn't belong in a table, it shouldn't be there. Joins and indexes on joined fields are what make a database run smoothly. simply having 2 larger table would require many table scans for queries, as well it makes data entry on the frontend side hell. Everything would have to be hard coded into the frontend, in terms of what fields to fill, and what Ids to associate to new records.
I guess we kinda bump heads a lot about this sorta thing, I mean I've only be working with SQL since early this year, but from what I read, it seems most people lean away from 'generic' style tables and packing everything into one or two tables. But this is a side note/rank.
In this example, you probably don't even need to create records in a separate table. You can generate it with a query the same way you are currently designing how to create the record, so one less table, but for ease in retrieving the data you could. I take it the user wants to query the table and you're not generating a report for them.
The schema of the Transaction table should look something like this:
[TransactionTable]
TransID (PK)
EquipmentID <not null>
TransTypeID <not null> FK
other columns
-I have gone a step further now and created a junction table that relates each type of transaction to a particular equipment type.
Yes, sounds good.
Other comments:
These comments are based on my not seeing the entire database schema.
I'm not sure the overall approach is best design practice and since you only have a 3 week deadline, you may not be able to make and test the necessary changes in time. Database design shouldn't be rushed; it needs to be deliberate and accurate which will save the DBA and developers many headaches in the future.
That said, what you are doing should work.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply