December 22, 2011 at 4:56 am
The unnecessary space by NULL values can be avoided if you use sparse columns.
http://msdn.microsoft.com/en-us/library/cc280604.aspx.
If you use design #1, you also have to add a new column in the transaction tables every time you add a new product. Historical records, which didn't have the option to purchase the new product, will get all NULL values for that new column.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 22, 2011 at 5:13 am
dev
I will keep the education in a seperate table add the referece to the profile table
Koen Verbeeck
For sparse it has disadvantage it will take additional 4 bytes + column data type size
if we stored values in the sparsed column.
December 22, 2011 at 5:21 am
dilipd006 (12/22/2011)
devI will keep the education in a seperate table add the referece to the profile table
But if a person doesn't have an education, you still have a NULL reference?
dilipd006 (12/22/2011)
Koen VerbeeckFor sparse it has disadvantage it will take additional 4 bytes + column data type size
if we stored values in the sparsed column.
Yes, but if you have a lot of NULL values (60% or more for varchar columns), you save space because the NULLs take up less space.
(and seriously, what are you using the run the database on? A commodore with a floppy drive? Space is nowadays not really an issue, unless you are storing terabytes of data)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 22, 2011 at 5:23 am
dilipd006 (12/22/2011)
devI will keep the education in a seperate table add the referece to the profile table
...
Good. How will you store it, in rows / columns?
If columns? You have to store NULLs. Then better be in Main table.
If rows? You might need a loop for one logical record. Performance hit.
I started the discussion mentioning DB design is always a Trade-Off. You have to set the priorities. Same applies to your Product table. I assume you can take the control from this point onwards, will design a good model.
If you still find difficulties hire a Professional.
December 22, 2011 at 5:42 am
How often are you likely to add new entities? Can I assume this is a travel agent or similar setup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2011 at 10:50 am
I would store the common information for the products in 1 table. Almost like a header table. Then store the details for a specific product in their own tables. For example, each product will have common characteristics that can all be stored in the 1 table. Then each individual product will have attributes that are distinct, and deserve their own table.
I used to work for a telephone book publishing company and they had a table that contained all of the information regarding the items of an order. The order could be for an ad, listing, or combination of both. Of course, an ad has different attributes from the listing. So the details about these attributes were stored in separate tables. When the details were needed, they could be retrieved. However, for a general query we only needed to fetch from the 1 table.
Jared
CE - Microsoft
December 22, 2011 at 3:29 pm
Koen Verbeeck (12/22/2011)
<snip>It is perfectly acceptable to have a denormalized database design in a relational database, for example in a data warehouse. SQL still works, and it is still pretty performant (even more than in a normalized database, as less joins are used to retrieve data)
I would have to disagree with you on this. Perhaps, that is just a bad example?
I'm not trying to say anything about you or your knowledge about data warehousing, but there are a lot of popular data warehousing techniques that are plain wrong. Denormalizing is very rarely a good practice.
December 22, 2011 at 9:20 pm
Hi
This is B2B and B2C travel application with end users will be travel agents and customers.
Yesterday I read the Generalization (SuperType/SubType)concept in DB designing, According to this concept if you identify different entities (Hotel,Flight,car,Transfer etc..) having same attributes in most case then it should be treated as one entity in this case it would be Product(SuperType).rest of the different attributes in each entity would be created as Subtype for the Product Table
Product(SuperType)
--below tables contain attributes to their specific entities
-- ProductHotel(SubType)
-- ProductCar(SubType)
-- ProductTransfer(SubType)
This link i refered for Object oriented design
http://msdn.microsoft.com/en-us/library/bb245675.aspx
Waiting for ur valuable inputs
December 22, 2011 at 10:37 pm
Lamprey13 (12/22/2011)
Koen Verbeeck (12/22/2011)
<snip>It is perfectly acceptable to have a denormalized database design in a relational database, for example in a data warehouse. SQL still works, and it is still pretty performant (even more than in a normalized database, as less joins are used to retrieve data)
I would have to disagree with you on this. Perhaps, that is just a bad example?
I'm not trying to say anything about you or your knowledge about data warehousing, but there are a lot of popular data warehousing techniques that are plain wrong. Denormalizing is very rarely a good practice.
I have to disagree on your disagreement. Denormalization is serving BI domain for many years and many successful implementations been done based on it. It was Denormalization that gave birth to ETL else why somebody need to pull Transactional Data and populate it in another database, 10 times bulky than original. Business was already satisfied with Transactional Reporting (I didn’t say Analytical Reporting).
I assume (please provide the feedback) you work (or have worked) on Teradata database. Teradata (and few more) is one of database solutions provider that prefers their data warehouse in 3rd Normal Form. And their proprietary hardware design supports it well. Databases like SQL Server & Oracle that provide hardware & platform independent database solutions can’t follow the same methodology.
I agree on one point that you raised here. OP (Dilip’s) requirements are for OLTP system, not for OLAP.
December 23, 2011 at 1:56 am
Honestly, with this kind of data I'd either go for the supertype/subtype or just completely separate tables. There's no real justification (other than generalisation) to go with one table design, considering how different the products are and how few of the attributes belong to more than one of them, and this is not an area where you could be adding a new product every week.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2011 at 2:39 am
Hi Gail
Thanks for the reply
I decided to go for seperate tables, as it would be easy to maintain.
As i mentioned earlier each search request will go to their corresponding table which improve the performance.
For ex:- If i have to search hotels it will bring the result from the Hotel table.Whereas in case of only one table design multiple product search request will hit the same table
December 23, 2011 at 4:03 am
dilipd006 (12/23/2011)
I decided to go for seperate tables, as it would be easy to maintain.
Easier to maintain maybe, probably easier to query, likely better performance and far less chance of data inaccuracies (like a hotel booking having a flight number and destination)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2011 at 6:03 am
GilaMonster (12/23/2011)
dilipd006 (12/23/2011)
I decided to go for seperate tables, as it would be easy to maintain.Easier to maintain maybe, probably easier to query, likely better performance and far less chance of data inaccuracies (like a hotel booking having a flight number and destination)
Not sure if this will make a difference, but my thinking is that the supertype/subtype may be a better option than completely separate tables if the assumption holds true that a customer may purchase more than 1 product at any one time. In which case you can get summary information from 1 table for all products, and details if needed from the subtables (is that even the right term?). In this case, assuming my business case, would it not perform better, be easier to maintain, and better data integrity? Just a thought 🙂
Jared
CE - Microsoft
December 23, 2011 at 6:29 am
Don't have enough info on the requirements to answer that one.
What I might do with something like this (again, might, we don't have the info to make hard decisions) is a generic 'bookings' table that has a reference to customer, to payment info, contains dates of booking, status and that kind of info, then the details of the individual bookings go in individual tables
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2011 at 7:11 am
Lamprey13 (12/22/2011)
I'm not trying to say anything about you or your knowledge about data warehousing, but there are a lot of popular data warehousing techniques that are plain wrong. Denormalizing is very rarely a good practice.
Any references?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply