Are the data types I've chosen the best choices?

Question

Are the data types I've chosen the best choices?

Viewing 8 posts - 31 through 37 (of 37 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100942 More actions · Answer 1

Create another Attributes document that has additional info about each attribute, including its valid range of values. This will end up being one of the most valuable documents created during the design phase.

For example:

Attribute Name  Data type  Max Length  Allowable values

Ticket Number   character  8           pattern of @#@@@#@#, where @ represents a letter and # a digit;
                                       the letters I, O and Z are not used (could seem to be a #)
Ticket Id       integer                1+, ever increasing
CPZ Code        character  3           1 to 3 alphanumeric/alpha-only (whichever) code
CPZ Name        character  50          full name of the CPZ; resprented by CPZ Code
...

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 2

The main thing is:

Do not get bogged down futzing about with specific data types during the design process!

Those details are best left until later anyway.

What is vital is to get the business rules and data requirements from the business people with that knowledge and get them documented.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

ScottPletcher wrote:

I would strongly recommend sticking with "integer" as the type. Add a domain (or range_of_values) property to limit the size rather than a data type name. Remember, the logical model is supposed to be generic, not tilted toward SQL Server.

This what confuses me about you. You say it's a "logical model" and we shouldn't worry about datatypes and then you make such a recommendation. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mr_X SSC-Addicted Points: 401 More actions · Answer 4

ratbak wrote:

What is Ticket ID, and why is it a tinyint? The name implies it's a unique ID. A tinyint would only allow 256 tickets (0-255). If it's a type or categorization, that make the attribute name reflect that.

Yes Scott also pointed this out. Ticket ID will at least be an INT. It was an oversight on my side.

ratbak wrote:

Why are you using both Ticket ID & Ticket Number as primary key for both Ticket & Response, and as the foreign key between the two?

The idea is that they work together to form a composite key. Ticket Number has many duplicates and so it would have been problematic for me to use it alone.

ratbak wrote:

But if both Ticket ID & Ticket Number comprise the primary key, you would be allowing for the same values to be used more than once in multiple combinations, and I don't think you want that.

Could you please give me an example here as I haven't fully understood what you mean.

ratbak wrote:

Are you confusing Scott's mention of a Ticket Response ID Scott suggested for Response?

I may well be as it was that that I changed into Ticket ID. How I understood it is that each Ticket Number be assigned an ID to handle the duplicate Ticket Numbers.

ScottPletcher wrote:

The Ticket Id is a numeric code that represents the Ticket Number. Each is, and must be, unique on its own. For example, Ticket Number A3FNF8N3 might be represented as Ticket Id 1.

Thanks for clarifying this. Ratbak I believe this should answer your question.

ScottPletcher wrote:

You'll get better input and a smoother conversation with business folks if you keep the tech out of it.

Are you suggesting that in the logical model I should show denote two PKs in a single table or just one?.

ScottPletcher wrote:

Create another Attributes document.

Just curious, what's the official name of this type of document?.

Thanks for all your help.

ratbak Ten Centuries Points: 1328 More actions · Answer 5

ratbak wrote:Why are you using both Ticket ID & Ticket Number as primary key for both Ticket & Response, and as the foreign key between the two?
The idea is that they work together to form a composite key. Ticket Number has many duplicates and so it would have been problematic for me to use it alone.

Jeff had mentioned there are duplicate ticket numbers in Response. Are you saying Ticket Numbers are duplicated even in ticket entities? I had hoped that wasn't true if one stripped out the data to only ticket attributes (Ticket #,Received,Type,Serviceable,Service Category), but there do appear to be ~ 187 duplicates, even w/ just those attributes... which indicates to me that either one or more of those are actually attributes of Response (or another entity), and/or the data is a mess. If there really are duplicate ticket numbers, can the data be fixed (merge, replace duplicate ticket numbers w/ new ticket numbers, and/or delete "junk" duplicates, etc.)? Given that users probably reference things by ticket number, those duplicates are going to cause confusion and complicate any UI or reporting using this data

ratbak wrote:But if both Ticket ID & Ticket Number comprise the primary key, you would be allowing for the same values to be used more than once in multiple combinations, and I don't think you want that.
Could you please give me an example here as I haven't fully understood what you mean.
ratbak wrote:Are you confusing Scott's mention of a Ticket Response ID Scott suggested for Response?
I may well be as it was that that I changed into Ticket ID. How I understood it is that each Ticket Number be assigned an ID to handle the duplicate Ticket Numbers.

Scott can respond, but given your descriptions, I don't think the intent was to have a composite key. There is no reason to use anything except TicketID as the primary key and foreign key. If Ticket Number is not unique, there is no value in carrying it over in a relationship -- it can be looked up using TicketID.

ScottPletcher wrote:The Ticket Id is a numeric code that represents the Ticket Number. Each is, and must be, unique on its own. For example, Ticket Number A3FNF8N3 might be represented as Ticket Id 1.

Thanks for clarifying this. Ratbak I believe this should answer your question.

If there are duplicate ticket numbers, then each Ticket ID will represent one Ticket Number, but a single Ticket Number could refer to multiple Ticket IDs/multiple tickets.

ScottPletcher wrote:You'll get better input and a smoother conversation with business folks if you keep the tech out of it.
Are you suggesting that in the logical model I should show denote two PKs in a single table or just one?.

A logical data model can have multiple candidate keys -- One or more attributes that define an individual row. But there should be only one primary key. Other candidate keys not defined as the primary key are called alternate keys.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 6

Jeff Moden wrote:

ScottPletcher wrote:
I would strongly recommend sticking with "integer" as the type. Add a domain (or range_of_values) property to limit the size rather than a data type name. Remember, the logical model is supposed to be generic, not tilted toward SQL Server.
This what confuses me about you. You say it's a "logical model" and we shouldn't worry about datatypes and then you make such a recommendation. 😀

You don't worry about physical data types vs logical ones. Take the example of "integer" vs "tinyint" we had above. "Integer" is a common, everyday term that should be familiar to anyone; "tinyint" is a technical type of int and could even vary from dbms to dbms. You need the generic data type so that everyone knows what the data is intended to be (and can adjust it if needed).

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 7

FYI, I did not look at the spreadsheet data. When I attempted to view it, it required a log in of some sort.

ScottPletcher wrote:

Are you suggesting that in the logical model I should show denote two PKs in a single table or just one?.

The model can show two candidate (potential) PKs (*). But I'd suggest not using the term "PK" / "FK" / etc. in the model you use to work with the business people, because such terms can be intimidating to non-IT people. (*) Ultimately on the physical side you'll have to pick one of the candidates to be the PK, but that decision can be delayed.

ScottPletcher wrote:

Create another Attributes document.
Just curious, what's the official name of this type of document?

Good q! I've seen it called different things. In the old days, we called it a "data dictionary" (which included other metadata as well).

Pick the term you prefer for it and use that in your environment.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Mr_X SSC-Addicted Points: 401 More actions · Answer 8

ratbak wrote:

...and/or the data is a mess. If there really are duplicate ticket numbers, can the data be fixed (merge, replace duplicate ticket numbers w/ new ticket numbers, and/or delete "junk" duplicates, etc.)? Given that users probably reference things by ticket number, those duplicates are going to cause confusion and complicate any UI or reporting using this data

This is a good point. I'm going to try to find out why this is. I have a feeling it could be the same ticket going through different stages of a process. But I'll double check.

ratbak wrote:

If Ticket Number is not unique, there is no value in carrying it over in a relationship -- it can be looked up using TicketID.

Now that you mention it, it is difficult for me to argue this case.

ratbak wrote:

A logical data model can have multiple candidate keys -- One or more attributes that define an individual row. But there should be only one primary key. Other candidate keys not defined as the primary key are called alternate keys.

So for example if I had two or more keys that would mean I have one primary key and one ore more alternate key/s? Is candidate key the same as an alternate key or are all keys (primary or not) called candidate keys?

ScottPletcher wrote:

FYI, I did not look at the spreadsheet data. When I attempted to view it, it required a log in of some sort.

When you go to the link just click this and the download will start automatically:

mediafire

Here is the link to the SQL Query for the entire table (I pasted it into a word doc as it's like 200+ pages): https://www.mediafire.com/file/cmio4ypw9hx8zxg/SQL_Correspondence.docx/file

ScottPletcher wrote:

The model can show two candidate (potential) PKs (*). But I'd suggest not using the term "PK" / "FK" / etc. in the model you use to work with the business people, because such terms can be intimidating to non-IT people. (*) Ultimately on the physical side you'll have to pick one of the candidates to be the PK, but that decision can be delayed.

Okay I'll stick to PK in that case. It seems that a lot of what's written on logical models isn't fully standardised but more based on what is and isn't commonly used. Would this be correct to say?.

ScottPletcher wrote:

In the old days, we called it a "data dictionary" (which included other metadata as well).

Thanks for sharing. I did a quick google search and I could already see a wealth of information on it.