Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
de4ever@gmail.com
Starting Member
36 Posts |
 Posted - 2010-03-22 : 00:03:00
|
| Hi All,I am having difficulty in designing some table.Scenario is : I have some matterials and theier specification.Each matterial has differenct types of specifications .Eg . Matterial1 has Spec1,Spec2,Spec3..SpecN specification ,Mattrerial2 may have n diferent specifications. So how can we design tables for these objects.Waiting for reply thanks |
|
|
rickymartin06
Starting Member
3 Posts |
Posted - 2010-03-22 : 00:53:05
|
| i this you need to create a table called materia and another table called specification with a 1 to many relationship, each materia can have 0 or more specifications having materia pk in specification table as a PK_FK, yo can also define a composed primary key having for example id_Materia + specification as a PK taht way you can have 1 materia with more than one specifications |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-22 : 02:56:43
|
I think it is m:n so you need a third table.MaterialM_Idother_colSpecsSp_IDother_colMaterial_Spec_TableM_SP_IDM_IDSp_IDThe Material_Spec_Table is used to get married the Materials with the Specs.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
de4ever@gmail.com
Starting Member
36 Posts |
Posted - 2010-03-22 : 04:10:08
|
quote:
Originally posted by webfred
I think it is m:n so you need a third table.MaterialM_Idother_colSpecsSp_IDother_colMaterial_Spec_TableM_SP_IDM_IDSp_IDThe Material_Spec_Table is used to get married the Materials with the Specs.
No, you're never too old to Yak'n'Roll if you're too young to die.
HI webfred,thanks for your reply.but in this solution one matterial with mulitple specs will have mulple records in Material_Spec_Table.So how i can i uniquely identify a matterial with diferent types of specifications like one resitor with 5% tolerance and 5ohm resisitance and another 10% tolerance and 10ohm resistance.thanks |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-03-22 : 06:25:12
|
quote:
HI webfred,thanks for your reply.but in this solution one matterial with mulitple specs will have mulple records in Material_Spec_Table.So how i can i uniquely identify a matterial with diferent types of specifications like one resitor with 5% tolerance and 5ohm resisitance and another 10% tolerance and 10ohm resistance.thanks
I'm not sure that this you should attempt to form some sort of key based on such properties. The reason are:1) You've said that you've got an unlimited list of possible properties.2) It's logical that two different materials could have (to the limits of measurement) identical properties.Therefore there doesn't appear to be a natural primary key based on the info you've provided.Webfred's suggestion is to have surrogate primary keys (because given the information posted there is no natural primary key) and then to have a key / value pair list of properties. Webfred's suggestion is slightly better than rickymartin06 because it's more normalised (you know that when you are talking about the property 'Resistance' then you are definitely talking about the same property for two different materials. If you just had two tables Materials / Properties then you'd have data duplication of the property name and possible ambiguity for the properties.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
de4ever@gmail.com
Starting Member
36 Posts |
Posted - 2010-04-05 : 04:34:41
|
Sorry to reply so late as I was busy with some oher assignments.My problem is how we can maintain stock quantity for each type of matterial with different specification and generate work order based on that ... waiting for your reply and Thanks quote:
Originally posted by Transact Charlie
quote:
HI webfred,thanks for your reply.but in this solution one matterial with mulitple specs will have mulple records in Material_Spec_Table.So how i can i uniquely identify a matterial with diferent types of specifications like one resitor with 5% tolerance and 5ohm resisitance and another 10% tolerance and 10ohm resistance.thanks
I'm not sure that this you should attempt to form some sort of key based on such properties. The reason are:1) You've said that you've got an unlimited list of possible properties.2) It's logical that two different materials could have (to the limits of measurement) identical properties.Therefore there doesn't appear to be a natural primary key based on the info you've provided.Webfred's suggestion is to have surrogate primary keys (because given the information posted there is no natural primary key) and then to have a key / value pair list of properties. Webfred's suggestion is slightly better than rickymartin06 because it's more normalised (you know that when you are talking about the property 'Resistance' then you are definitely talking about the same property for two different materials. If you just had two tables Materials / Properties then you'd have data duplication of the property name and possible ambiguity for the properties.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
|
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-05 : 04:52:22
|
| well based on what you've told us about the specification (an expandable list of properties) then I'd be tempted to go with something likeTABLE material (<Something that uniquely identifies the material to you> -- this could be a brand name or some other descriptor. There has to be something unique for you to buy this material from someone -- a name etc.<primary key> -- either the unqiue identifier earlier or (I'd be more tempted for this) a surrogate primary key)TABLE properties (<primary key> -- I'd go for a surrogate primary key<Property> -- the name of the property (resistance, ductility, etc)<propertyDescription> -- A description of the property (so you could differentiate different types of resistance etc. I'd still be tempted to make the property field unique though)TABLE materialProperties (<primary key> -- surrogate autonumber.<materialID> Foreign key to material table<propertyID> Foreign key to property table)-- You could easily form a natural primary key with materialId and propertyId instead. If you did I'd be tempted to make the primary key a NON CLUSTERED index and put a clustered index on an autonumber column because that way when you insert new property links the inserts will be nice and fast.TABLE runningStock (<primary key> -- autonumber<materialID> -- foreign key to material<quantity>)Of course this is all rough and ready, but it does let you bring back a nice list of a meterial's properties.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Database design problem