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).
How should I set up the tables?
TypeA( common fields + unique fields )
TypeB( common fields + 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?
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!
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:
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.
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.
Table_System_Data // Each system controls more than one line
Message_ID (fk) (&pk?)
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?
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?
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?
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
Table_Error \\ Total fields: 5
Message_ID (fk) (&pk?)