June 8, 2017 at 7:23 am
I realise I am late to the game here, but one thing I tend to try to adhere to is not using the PRIMARY filegroup for user defined tables.
whilst it is acceptable, I try to keep all the default database metadata separate, from my user data.
Create a user-defined filegroup and make it the default, put a new data file in with a size restriction.
This way, if something goes wrong with the application and the user-defined filegroup is filled up, I still have a chance of saving the PRIMARY filegroup.
Also, strongly consider the need for NULLs. Depending on who you talk to some will say NULLs are bad, or NULLs are hard, and some will say NULLs are good or NULLs are necessary.
Don't avoid using NULLs just to create data, but consider the volume of NULL entries you will have against not NULLs, and think about whether you need the column, or if you can implement it in another way.
June 11, 2017 at 10:07 am
DimPerson - Thursday, June 8, 2017 7:23 AMI realise I am late to the game here, but one thing I tend to try to adhere to is not using the PRIMARY filegroup for user defined tables.
whilst it is acceptable, I try to keep all the default database metadata separate, from my user data.
Create a user-defined filegroup and make it the default, put a new data file in with a size restriction.
This way, if something goes wrong with the application and the user-defined filegroup is filled up, I still have a chance of saving the PRIMARY filegroup.Also, strongly consider the need for NULLs. Depending on who you talk to some will say NULLs are bad, or NULLs are hard, and some will say NULLs are good or NULLs are necessary.
Don't avoid using NULLs just to create data, but consider the volume of NULL entries you will have against not NULLs, and think about whether you need the column, or if you can implement it in another way.
Thanks for the advise Dim !!!
I will design the tables accordingly.
June 11, 2017 at 10:10 am
dmbaker - Thursday, June 8, 2017 6:52 AMIs there a reason you have separate "open" and "closed" tables? They appear to be identical, with the only logical difference being a "status" of the item contained ("open" or "closed" I guess). In other words, should you really have a single table with a "status" column and store both open and closed items in one table?I'm not saying that what you have is wrong, I'm just saying it's something that makes me ask the question, and perhaps you should document the reason why you went with separate tables if there's a reason for it. That'll help someone who later inherits your system to understand why it's designed the way it is. I can think of valid reasons for separate tables, but I can only assume why you designed it that way. Don't make me assume, please, that's a bad idea. 🙂
Hi DMBaker,
Thanks for the advise ...
You are correct, I think there is no need to have two tables, So I am going to have one staging table to load both open and closed items and process the data based on status.
June 11, 2017 at 11:20 am
Hi All,
Based upon the suggestions provided I have created tables and given appropriate foreign keys.
Also I have given the datatypes for columns based on below consideration :
Sample Data : User Name, Account Name
Data Type : VarChar
Sample Data : All Primary Keys which increments by 1 :
Data Type : Int
Sample Data : Fixed Characters length like 10,15..
Data Type : Nchar(10), Nchar(15).
I have attached the table structure and database diagram in the below files.
The three Core tables in the relationship diagram are
1. TPAOpenWorkitem (Contains work items)
2. Issue (Contains Issues)
3. MasterAssigmnet ( contains whom the work items should be assigned to)
Please check it and let me your suggestions ...
June 13, 2017 at 2:32 pm
Jackson89 - Saturday, June 3, 2017 11:25 PMNo, this is not a table. By definition, a table must have a key and this thing does not. It's a deck of punch cards. Now let's go down line by line in your DDL. The first thing as it having open and closed items in separate tables is the equivalent of having a "male_personnel" and a "female_personnel table" in a schema; Chris Date and David McGovern have done articles on this design flaw. I have called it "attribute splitting" in my work. You see being opened or closed, is not a whole different kind of thing; it's a status! These should be in the same tableWhat you call a "company_code" should use some industry-standard instead of something you made up. I would recommend the DUNS which is often required by law in US industries and in European countries. It. It is CHAR(9), and all digits; Google it.
You really have a variable length journal ID of 30 characters? Please look at most of the accounting packages I have seen in the last 40 years of programming; they use fixed length account names or numbers.
Your effective date should have a default of CURRENT_TIMESTAMP or something. Your design has more nulls than I have ever written in the table in 30 years of doing SQL. Did you know that sequence is a reserved word in SQL with special meaning? The rest of your columns with things like "line_id" seem like physical descriptions of paper. We don't do physical descriptions of paper forms in RDBMS; we have an abstract model of data that's what we want to use. I'm going to assume your currency code is the ISO standard, but your amount column is name too generically. And it should never, never, never be the old Sybase money data type. That existed to keep COBOL programmers happy. Google about the rounding errors in its arithmetic; it doesn't work!
Anytime I see somebody writing NCHAR(1) I immediately gets scared that they are using assembly language style flags and not building a relational model. Assembly languages use flags; SQL does not.
Column names like "source", "operator" need to have what ISO 11179 calls attribute property. Is this the operators name, his wife's name issue size, his salary or what? To be is to be something in particular; to be nothing in particular or anything in general is to be nothing at all – – remember that for your first logic class? It's called the law of identity is the foundation of all Western thought.
Without knowing your specs, I would guess that things in suspense are logically different entities from journal entries and perhaps we should have some other tables.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply