Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    distinguish between "known" and "unknown" - use 2 tables?

    Hi all,

    I know my thread title is not very meaningful. Could not think of a better way to name the thread.

    What I have is m:n relation resolved in 3 tables:

    tblMixture
    tblPart
    tblMixturePart

    So we have a "Mixture" which is made up of "Parts". This is simple at first glance but the problem now is that not every "Part" is known or identified.
    I know it is in the Mixture, but I do not know what it is.
    Anything unknown can appear multiple times in the "Parts" table.

    Now there is the case where an Unknown Part is identified. This Part should now get a new "Part-Number". The Part number is to a certain extent dependent on what the part actually is. (Note: this is an existing numbering schema which can't be changed). Unknown parts should just be sequentially numbered, like "u000001" (they need some kind of number so they can be linked to tblMixturePart and the numbering must therefore match the Numbering system of the identifed Parts, text with 7 characters. That's more than enough since this table will never be very big).

    Assume "u000001" is now identified and gets a new "Part-Number" "p000001".
    We enter a new record to the table with number "p000001". Now I would like to also have a link between "p000001" and "u000001", so that I know that "u000001" is the same as "p000001". Just changing does not work because as example "u000941" could be the same as "u000001" and therefore as "p000001" ("Part-Numbers must be unique) and the history must be kept. So I must still know, that the "Part" was unidentified at the time it was discovered in a mixture.

    I'm not very clear what's the best way to model this. One idea is to have 2 tables, 1 identified and 1 unidentified. On identifiication the 2 part numbers "u000001" and "p000001" are linked in a "Link-table". But I see alot of issues with such a solution "Parts" are "Parts" so they should be in the same table.

    (The reason behind this is, that there will be exactly 1 users that adds Identified entries but many that enter unkowns. But again that seems to be a buisness rule and has nothing to to with the database, right?)

    A second Idea is to make a boolean field "Identified true/false" and then either make 2 "Part-Number" fields: "InitalPartNumber" and "CurrentPartNumber". "InitalPartNumber" is unique and fixed whereas "CurrentPartNumber" can contain duplicates and may also change (to be exact it will change only once and only if it prevously was unidentified).
    To me this seems more practical. Downside onyl seems to be alot of duplicate data. Is this a good way to go?

  2. #2
    Join Date
    Jun 2009
    Posts
    5
    If the same unidentified part will NOT be related to multiple mixtures then "InitalPartNumber" and "CurrentPartNumber" can be present in tblMixturePart. The two categories can be in two seperate tables are one single table based on response to the following:

    Are there too many attributes
    - which will be NULL for unidentified parts and will have proper values for identified parts
    OR
    - with values duplicated between
    unidentified and corresponding identified part?

  3. #3
    Join Date
    Jun 2009
    Posts
    5
    Quote Originally Posted by joatmon
    If the same unidentified part will NOT be related to multiple mixtures then "InitalPartNumber" and "CurrentPartNumber" can be present in tblMixturePart. The two categories can be in two seperate tables are one single table based on response to the following:

    Are there too many attributes
    - which will be NULL for unidentified parts and will have proper values for identified parts
    OR
    - with values duplicated between
    unidentified and corresponding identified part?
    there sure will be attributes that are NULL for unidentified parts. But duplication will be limited because the idea is that once a Part is identified a new entry will be made and the "CurrentPartumber" in all unidentifed parts, that actually are this new identifed part, is changed to the "InitialPartNumber" of the newly created Part. Attrubutes can then easly be queried using the "CurrentPartNumber".

    I tried to model the databasewith 2 tables using MS Access. But liek that it seemed very complicated to get things done.
    Short example:
    I have a Mixture "A". I want to see all "Parts" of Mixture "A" and some attributes of each of these parts. This basically means i have to make a union query on the 2 tables.
    Then also I'm not sure how to implement relations (referential integrity) since "PartNumber" in tblMixturePart can be in either of the 2 tables. Is that possible at all?

  4. #4
    Join Date
    Jun 2009
    Posts
    5
    Q: how to implement relations (referential integrity) since "PartNumber" in tblMixturePart can be in either of the 2 tables. Is that possible at all?

    A: If you have 2 (NULLable)attributes InitalPartNumber and CurrentPartNumber in tblMixturePart, then you can define 2 relationships (both columns can point to same table or different tables). Not possible with a single attribute.

    For the design of inserting unidentified part number in tblMixturePart.CurrentPartNumber and subsequently updating it with identified part number (and InitalPartNumber with unidentified part number), it is better to have a single tblPart table. Your query will always be using CurrentPartNumber to join with tblPart. The disadvantage is the round-about way of determing CurrentPartNumber for a given InitalPartNumber (or vice-versa), which may not be required at all.

  5. #5
    Join Date
    Jun 2009
    Posts
    66
    Table splitting is never a good idea, you have bad design and need patchwork on top of patchwork to fix it.

  6. #6
    Join Date
    Jun 2009
    Posts
    5
    Quote Originally Posted by sqlguru
    Table splitting is never a good idea, you have bad design and need patchwork on top of patchwork to fix it.
    Yeah I'm pretty much coming to this conclusion as well, since it seems impossible to get what I want with 2 tables. You are asking yourself why I even think about this? It's simple. There are already alot of Identified parts available. And whoever did the work to get there is not very keen on mixing "his" unique data with unknowns. Of course it actually is very simple to distinguish between the 2 with a boolean field and in the the presentation layer they will actually appear as 2 different types. But that's not easy to explain...and understand.

    A: If you have 2 (NULLable)attributes InitalPartNumber and CurrentPartNumber in tblMixturePart, then you can define 2 relationships (both columns can point to same table or different tables). Not possible with a single attribute.
    But then I can't make "mixturenumber" and "(initial)Partnumber" in tblMixturePart a combined primary key or unique constraint.

  7. #7
    Join Date
    Jun 2009
    Posts
    66
    Maybe I don't understand the problem but why can't you enter the unknown part in the parts table (u00001, u00002, u00003 etc) and then when you know the part, change the u00001 to p00001 (you would check if that part number already existed, put a unique constraint etc). If you have referential integrity setup, a cascade would automatically reflect the change in the mixture parts table.

    To get the next part number, probably have a "identified date" column instead of a flag (WHEN is better than IS because you can deduce IS from WHEN but not the other way around)

    DECLARE @NextSequentialPartNumber INT;
    SELECT @NextSequentialPartNumber = COUNT(*) FROM dbo.parts WHERE part_identified IS NOT NULL;
    SET @NextSequentialPartNumber = @NextSequentialPartNumber + 1;

    DECLARE @PartNum;
    SET @PartNum = 'p' + CAST(REPLACE(STR(@NextSequentialPartNumber ,6),' ','0') AS VARCHAR(25));
    Last edited by sqlguru; 06-24-09 at 08:53.

  8. #8
    Join Date
    Jun 2009
    Posts
    5
    Quote Originally Posted by sqlguru
    Maybe I don't understand the problem but why can't you enter the unknown part in the parts table (u00001, u00002, u00003 etc) and then when you know the part, change the u00001 to p00001 (you would check if that part number already existed, put a unique constraint etc). If you have referential integrity setup, a cascade would automatically reflect the change in the mixture parts table.

    To get the next part number, probably have a "identified date" column instead of a flag (WHEN is better than IS because you can deduce IS from WHEN but not the other way around)

    DECLARE @NextSequentialPartNumber INT;
    SELECT @NextSequentialPartNumber = COUNT(*) FROM dbo.parts WHERE part_identified IS NOT NULL;
    SET @NextSequentialPartNumber = @NextSequentialPartNumber + 1;

    DECLARE @PartNum;
    SET @PartNum = 'p' + CAST(REPLACE(STR(@NextSequentialPartNumber ,6),' ','0') AS VARCHAR(25));

    Of course you are right. It should be possible. But I've read changing data in key columns is rather bad practice. I'm not an expert so I can't really decide if that is the case.
    Date_field sound a good idea but I would still need the flag since an empty "identified date" does not mean it is not identified. (legacy data as example of which no such date is available). Or said otherwise I need a fast and simple way to select all knows and all unknows and a flag is very handy for that. (to the users these parts will be presented separatley from each other as if the are 2 different things stored in a different place).

Posting Permissions

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