Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2003
    Posts
    12

    Question I'm new, please help me, thanks

    All,
    I'm very new with database design. Please help me with the following attachment. Thanks a lot.
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: I'm new, please help me, thanks

    Originally posted by gobeach
    All,
    I'm very new with database design. Please help me with the following attachment. Thanks a lot.
    There's not really enough information in your attachment to produce a data model, but it looks to me like the Color is independent of the Serial and Grade, so the data model should be more like:

    Color >-- Alloy --< Serial --< Grade

    It's not clear whether alloys other than Steel-Alloy have serial numbers, grades and colours also.

    You seem to be over-using surrogate keys in your design - e.g. why introduce a grade_id of 1 for the grade 'A'? The grade itself is a better key. On the other hand, you are using alloy_name as a key, where perhaps an alloy_code would be a good idea, since alloy_name is quite long, is mixed case, and a likely candidate for updates.

  3. #3
    Join Date
    Apr 2003
    Posts
    12

    Re: I'm new, please help me, thanks

    Thanks a lot. I fixed some problems and still have some questions. Could you please take a look? Thanks.




    Originally posted by andrewst
    There's not really enough information in your attachment to produce a data model, but it looks to me like the Color is independent of the Serial and Grade, so the data model should be more like:

    Color >-- Alloy --< Serial --< Grade

    It's not clear whether alloys other than Steel-Alloy have serial numbers, grades and colours also.

    You seem to be over-using surrogate keys in your design - e.g. why introduce a grade_id of 1 for the grade 'A'? The grade itself is a better key. On the other hand, you are using alloy_name as a key, where perhaps an alloy_code would be a good idea, since alloy_name is quite long, is mixed case, and a likely candidate for updates.
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: I'm new, please help me, thanks

    Originally posted by gobeach
    Thanks a lot. I fixed some problems and still have some questions. Could you please take a look? Thanks.
    Your questions:

    1) I tried to re-design like below, is that correct with those example data?

    It looks like it may be correct.

    2) What table should I add the Thickness and Rate fields on?

    I don't know. What do they depend on? Quite possibly they belong in a new table where the user is creating a record, e.g. "I want a Serial 5000 Steel-Alloy with a thickness of .5" Is there a fixed list of thicknesses per Serial Number, or per Alloy, or what? If so, you want a table for those also.

    3) Could I name for the Serial like below?

    Don't understand the question, sorry.

    You should get a book on database design, and learn about functional dependencies and normalisation, etc.

  5. #5
    Join Date
    Apr 2003
    Posts
    12

    Re: I'm new, please help me, thanks

    Thanks for your answering quickly.
    I still don't understand why Color Table is independent like you said. Why it has relationship with Alloy Table.
    What's way should i go when try to retrieve data like Steel-Alloy 5000 A Black? Thanks.


    Originally posted by andrewst
    Your questions:

    1) I tried to re-design like below, is that correct with those example data?

    It looks like it may be correct.

    2) What table should I add the Thickness and Rate fields on?

    I don't know. What do they depend on? Quite possibly they belong in a new table where the user is creating a record, e.g. "I want a Serial 5000 Steel-Alloy with a thickness of .5" Is there a fixed list of thicknesses per Serial Number, or per Alloy, or what? If so, you want a table for those also.

    3) Could I name for the Serial like below?

    Don't understand the question, sorry.

    You should get a book on database design, and learn about functional dependencies and normalisation, etc.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: I'm new, please help me, thanks

    Originally posted by gobeach
    Thanks for your answering quickly.
    I still don't understand why Color Table is independent like you said. Why it has relationship with Alloy Table.
    What's way should i go when try to retrieve data like Steel-Alloy 5000 A Black? Thanks.
    I only said it LOOKS like it is independent. It's all about functional dependency, i.e. what governs the choice of colour?

    If all serial numbers of an alloy are available in the same set of colours, i.e. choice of colour is INDEPENDENT of serial number, then they should be related to the Alloy table not the Serial table. On the other hand, if Serial 5000 is available in say black,red or blue but Serial 6000 is available in red or green, then the choice of colour is DEPENDENT on Serial, so the relationship should be with the Serial table.

    What does it mean to "retrieve data like Steel-Alloy 5000 A Black"? Are you in the process of creating a record of an instance of something, e.g. the purchase/sale of something that is made of "Steel-Alloy 5000 A Black"? In which case, your screen would probably work something like this:
    1) User selects Alloy: Steel-Alloy
    2) User selects Serial Number: 5000 --> FROM the list of Serial Numbers that are valid for Steel-Alloy
    3) User selects Grade: A --> FROM the list of Grades that are valid for Serial Number 5000
    4) User selects colour: Black --> FROM the list of Colours that are valid for [Steel-Alloy or Serial Number 5000, depending on dependency above]
    5) User specifies width, etc.
    6) Record created in table X

    Table X would look something like this:

    CREATE TABLE x
    ( x_id NUMBER PRIMARY KEY
    , serial_no NUMBER REFERENCES Serial
    , grade VARCHAR2(1) REFERENCES Grade
    , color_id NUMBER REFERENCES Color
    , width NUMBER
    , ...
    );

  7. #7
    Join Date
    Apr 2003
    Posts
    12

    Re: I'm new, please help me, thanks

    Your explanations were really good with examples. Now I can understand about it but I still have question (sorry for too much questions J)
    How’s about if I have both independent and dependent like:

    If I have Serial 4000, 4500,5000 of Steel-Alloy is same set of colours (Black, Silver) but Serial 6000 of Aluminum Alloy has Red, White – Serial 6500 of Aluminum Alloy has Green, Blue? What’s the relationship connect to?
    Is that Color connect to both Alloy and Serial? Thanks.


    Originally posted by andrewst
    I only said it LOOKS like it is independent. It's all about functional dependency, i.e. what governs the choice of colour?

    If all serial numbers of an alloy are available in the same set of colours, i.e. choice of colour is INDEPENDENT of serial number, then they should be related to the Alloy table not the Serial table. On the other hand, if Serial 5000 is available in say black,red or blue but Serial 6000 is available in red or green, then the choice of colour is DEPENDENT on Serial, so the relationship should be with the Serial table.

    What does it mean to "retrieve data like Steel-Alloy 5000 A Black"? Are you in the process of creating a record of an instance of something, e.g. the purchase/sale of something that is made of "Steel-Alloy 5000 A Black"? In which case, your screen would probably work something like this:
    1) User selects Alloy: Steel-Alloy
    2) User selects Serial Number: 5000 --> FROM the list of Serial Numbers that are valid for Steel-Alloy
    3) User selects Grade: A --> FROM the list of Grades that are valid for Serial Number 5000
    4) User selects colour: Black --> FROM the list of Colours that are valid for [Steel-Alloy or Serial Number 5000, depending on dependency above]
    5) User specifies width, etc.
    6) Record created in table X

    Table X would look something like this:

    CREATE TABLE x
    ( x_id NUMBER PRIMARY KEY
    , serial_no NUMBER REFERENCES Serial
    , grade VARCHAR2(1) REFERENCES Grade
    , color_id NUMBER REFERENCES Color
    , width NUMBER
    , ...
    );

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: I'm new, please help me, thanks

    Originally posted by gobeach
    Your explanations were really good with examples. Now I can understand about it but I still have question (sorry for too much questions J)
    How’s about if I have both independent and dependent like:

    If I have Serial 4000, 4500,5000 of Steel-Alloy is same set of colours (Black, Silver) but Serial 6000 of Aluminum Alloy has Red, White – Serial 6500 of Aluminum Alloy has Green, Blue? What’s the relationship connect to?
    Is that Color connect to both Alloy and Serial? Thanks.
    I think this is a matter of choice. Since some Serials have their own set of colours, you need to be able to define colours per Serial. For those Serials that have the same set of colours you can do one of the following:

    1) Repeat the set of colours in same table for each Serial:
    Serial,Colour
    4000,Black
    4000,Silver
    4500,Black
    4500,Silver
    5000,Black
    5000,Silver
    6000,Red
    6000,White
    6500,Green
    6500,Blue

    2) Do not hold set of colours for these Serials, but hold a default set against the Alloy - this only works if there is only one group of Serials with shared colours:

    Alloy,Default Colour
    Steel-Alloy,Black
    Steel-Alloy,Silver

    Serial,Colour
    6000,Red
    6000,White
    6500,Green
    6500,Blue

    3) Create a new ColourGroup table and associate each Serial with a ColourGroup

    ColourGroup,Colour
    1,Silver
    1,Black
    2,Red
    2,White
    3,Green
    3,Blue
    4,Red /* This group just to show that a colour can be in >1 group */
    4,Black

    Serial,ColourGroup
    4000,1
    4500,1
    5000,1
    6000,2
    6500,3

    It is up to you which approach is most appropriate. Option 1 is the easiest to work with, so I'd go for that unless the repetition of data seems excessive.

  9. #9
    Join Date
    Apr 2003
    Posts
    12

    Re: I'm new, please help me, thanks

    Thanks for quick responding. Yeah, I think option 1 is easier than.
    Now, it looks like: Alloy ---< Serial, Color >--- Serial ---< Grade

    I have a question on Grade table. Since only one Serial 5000 has Grade on it, so can I combine Grade table by putting Grade on Serial Table and set Grade value to null (that’s the option for user, if user choose Serial 5000 then it’ll pop up list of Grade field for entering, otherwise, it won’t pop up a list)?
    And now it looks like: Alloy ---< Serial (included Grade field) ---> Color



    Originally posted by andrewst
    I think this is a matter of choice. Since some Serials have their own set of colours, you need to be able to define colours per Serial. For those Serials that have the same set of colours you can do one of the following:

    1) Repeat the set of colours in same table for each Serial:
    Serial,Colour
    4000,Black
    4000,Silver
    4500,Black
    4500,Silver
    5000,Black
    5000,Silver
    6000,Red
    6000,White
    6500,Green
    6500,Blue

    2) Do not hold set of colours for these Serials, but hold a default set against the Alloy - this only works if there is only one group of Serials with shared colours:

    Alloy,Default Colour
    Steel-Alloy,Black
    Steel-Alloy,Silver

    Serial,Colour
    6000,Red
    6000,White
    6500,Green
    6500,Blue

    3) Create a new ColourGroup table and associate each Serial with a ColourGroup

    ColourGroup,Colour
    1,Silver
    1,Black
    2,Red
    2,White
    3,Green
    3,Blue
    4,Red /* This group just to show that a colour can be in >1 group */
    4,Black

    Serial,ColourGroup
    4000,1
    4500,1
    5000,1
    6000,2
    6500,3

    It is up to you which approach is most appropriate. Option 1 is the easiest to work with, so I'd go for that unless the repetition of data seems excessive.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •