Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    9

    Question Suggestions for new database design?

    Greetings to all you database gurus out there, I could really use your help.

    I'm designing a new database using MS SQL Server 2000. The database will hold messages generated from an application. There are two different types of messages, but both types have fields that are common between them. I would like to have a MessageID as the primary key for both types (ie. a message of TypeA could not have the same MessageID of a TypeB message).

    Question #1
    How should I set up the tables?

    TypeA( common fields + unique fields )
    TypeB( common fields + unique fields )

    or

    Message( common fields )
    TypeA( unique fields )
    TypeB( unique fields )

    If I use the first setup, is it possible to have a primary key that spans multiple tables? If I use the second setup, would I just relate the tables with primary / foreign keys? Are there any other ways to set up the tables?

    Question #2
    Part of the message that I want to store has array type information. Is there a good way to store arrays in SQL Server? The arrays are not fixed length, and I would like to keep up with the array index and value, and be able to search on both.

    I know this is very general information, but I'm really looking for concepts. If I need to get more specific, I would be happy to.

    I'm also willing to do my homework on this, so if you wanted to point me to reference material, that would be fine. I would prefer that it be somewhat specific however and not just "Go read SQL Server Books Online." Your thoughts and suggestions would be greatly appreciated!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Well, InquisIdiot, question #1 is a very fine point that depends upon a lot of factors specific to your database. How many common fields, how many unique fields, how many records of each, are you going to be running queries that need to return records from both set of data simultaneously, etc.

    Don't be afraid to post your actual fields for us to look at. Chances are good that I or another member have designed a similar application and may be able to give some specific advice.

    I will give you this advice:

    Your array needs to be stored as (at least one) separate table, linked to the other message table(s). This leads me to favor your second design option.

    If you use separate tables, then give them each an index (or unique key) that uses the UniqueIdentifier datatype and defaults to the new_id() function. This will give you a lot of flexibility as far as splitting and combining your message and array tables while still ensuring that each record can be uniquely identified.

    There is a third data-structure that might suit your circumstances:
    Message_Table
    Message_ID
    Common_Fields
    Message_Type

    Type_TableA
    Message_ID
    TypeA_Fields

    Type_TableB
    Message_ID
    TypeB_Fields

    Message_Details
    Message_ID
    Array_Data

    This schema is fully-normalized, but some denormalization may be appropriate for your database.

    Like I said, how you go with this depends completely on your application, so tell us more about it and you'll get more guidance.

    blindman

  3. #3
    Join Date
    Aug 2003
    Posts
    9

    Post

    Thanks for your reply blindman. More information about the database follows:

    The messages will be coming from a manufacturing plant. There are two types of messages, status and error. Both messages contain common information such as message ID, message version, time message was generated, etc. There are approx. 7 common fields, 4 error fields, and 5 status fields.

    The status fields are where I have to deal with arrays, because each manufacturing line in the plant (and the system that controls it) has its own status information.

    Around 8 status messages will be generated every day, and error messages will be generated as they occur.

    I probably need the complete message data in most of my queries.

    How does the following design look?

    Table_Message
    Message_ID (pk)
    Common_Fields
    Message_Type

    Table_Error
    Message_ID (fk) (&pk?)
    Error_Fields

    Table_Line_Data
    Message_ID (fk) (&pk?)
    Line_Number (pk)
    Line_Data_Fields

    Table_System_Data // Each system controls more than one line
    Message_ID (fk) (&pk?)
    System_Number (pk)
    System_Data_Fields

    Question #1
    Is there a need for a Status table? I decided not to include one because all of the status info is contained in the Line_Data and System_Data tables. Is there another reason I might want one?

    Question #2
    Can a foreign key also be a primary key? For example, there will only be one entry in the error table for each Message_ID. That means it is a one-to-one relationship, correct? How do you create those in SQL Server 2000?

    Question #3
    In the Line_Data and System_Data tables, I think I can use a combination of the Message_ID and Line/System_Number fields as a primary key. Is that a good idea?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    It's rare that I recommend this, but looking at your situation I think you can greatly simplify your queries without incuring a big penalty by going with a schema that is less than fully normalized.

    Message_Table
    (common fields)
    (Status fields)
    (Error fields)

    You'll end up with NULL values in each record, but that is a small price to pay.

    When you say that each line has its own status information, do you mean info beyond the five status fields? An example would be helpful.

    blindman

  5. #5
    Join Date
    Aug 2003
    Posts
    9
    The problem with storing all the information in one table is that the number of lines in each plant is variable. There could be up to 8 (maybe more, but not likely) and I would like to keep my application as open to change as possible. Looking back over my posts, I didn't mention that I will be storing data from multiple plants (probably in the hundreds) in one location. I don't know if that would have changed your advice or not.

    Will I incur a performance hit by having a fully normalized structure with as few fields as I have? Do you still recommend putting all of the fields in one table?

    Table_Message \\ Total fields: 7
    Message_ID (pk)
    Common_Fields (5)
    Message_Type

    Table_Error \\ Total fields: 5
    Message_ID (fk) (&pk?)
    Error_Fields (4)

    Table_Line_Data \\ Total fields: 4
    Message_ID (fk) (&pk?)
    Line_Number (pk)
    Line_Data_Fields (2)

    Table_System_Data \\ Total fields: 4
    Message_ID (fk) (&pk?)
    System_Number (pk)
    System_Data_Fields (2)

Posting Permissions

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