SQL database design

[Wimnat]

I suck at database design so I have no idea what to do with this data...

I have recorded some data from voip calls. there are different types of packet in each call i.e. sip and rtp. I want to write each packet to a database.

Should i;

• Create two tables - one for sip packets from every call and one for rtp packets from every call
• Create a table for each call which accepts all the fields from rtp and sip (a lot of fields will get left blank)

Or is there a (c)? I've no idea so please help!

 

[Hades]

I don't know much about voip so not sure what is meant by sip and rtp.There is an option c:

One table
A data field that can accept either sip or rtp packets.
A type field indicating whether its sip or rtp.

 

[Wimnat]

Each packet has many different fields.

A SIP packet may have say 5 fields like "to" and "from" etc

An RTP packet will have a different number of fields with things like "version", "identifier" etc.

 

[Mr^B]

When the design is not clear cut you need to start looking at how you are going to query the data.

Once you've done that an answer (from the two you have posted) may become clearer.

In the true "OO" sense, you should have 4 objects:

Call
Packet (Abstract)
STP Packet : Packet (Inherits base properties from Packet)
RTP Packet : Packet (Inherits base properties from Packet)

Which would mean 3 tables (Call will hold the "meta-data" about the call, such as a Call ID (either supplied or generated by the database) and other things like date/time, from IP, to IP, that sort of thing.

If you supply a full list of everything that you receive and likely queries that will be made on the data, it'll make it easier to determine which is the correct route to take.

 

[JIMA]

Hi,

Personally I'd go for the two tables approach (one for sip and one for rtp). Putting different types of data on a single table can lead to confusion, especially where different fields are filled for each type.

If the different call types have some common attributes such as CallID and maybe call-time, call-length etc., then having a separate table of these applying to both types of call would be a good idea (as Mr^B suggested).

If you want to select on either sip or rtp e.g get calls of that type made between two dates, then you can join the common-element table to just one of the tables. If you want to get data from both types of call you could either join on both sip/rtp tables or use a Union in your select or (if your DBMS allows) create a view to bring the relevent bits of the tables together so they can be queried together.

Hope that helps,
Jim