September 2, 2008 at 12:06 pm
Hello,
From currently working on a large personal project, I have several database design dilemmas:
Is having a table with a lot of relationships coming into it, a bad idea?
For example, I have a database with tables for carparts (exhaust, wheels, and so on), and all of these relate to the fitting table which will, amongst other things, store fitting notes. Is this bad design?
I am using SQL Server 2005 and does it matter which way you connect two tables? ie between fitting and wheel, when dragging the relationship arrow.
If I have a suspension table, fitting, and also a product table, what query could I use to get x back, using a stored procedure?
I want to replicate between a database optimised for write and one optimised for read. What can I do to optimise a db for read/write? If I have a database operated for read, then wouldn't it be best to have lots of small tables rather than one big table. BUT for a database which gets written to, this may not be the case. When replicating, don't both tables need exactly the same schema?
Finally, the site I am making will allow users to sell parts to users, but also, the site will have part statistics. For example, an exhaust which adds x bhp, and the generic information, such as weight, length, etc.
Is this a right strategy:
I have 2 tables for descriptions, names, etc. Because when I add a product, I give it my own description and name, but a user might want to give a different name and description for the same product, which he will be selling. But I want to reference the same product in the product table. So for example, if I add a new product, called exhaust1, add write a name/description, a user will write their own name/description for exhaust1.
Basically the same product can be used for two different site scenarios and I don't want to break database design rules because of data repetition or something like that.
Also, there are certain legislation requirements databases have to meet, after the scandal with Anderson Consulting. Where can I get info on this?
September 3, 2008 at 6:46 am
Holy cow, that's a ton of HUGE questions. Most of them can be answered, it depends.
To start with, it is possible to have too many relationships, but to determine if you have too many, we'd need to see a lot more of the design and requirements than you've laid out so far. Based on what you described, I don't think you're in any danger.
How to query the database you built is an enormous question. I'd suggest you pick up a copy of Itzik Ben-Gan's book Inside SQL Server 2005: T-SQL Querying. You might also want his other book, T_SQL Programming.
Based on what you've said so far, you might want to do a web search and look at a few designs for a common problem called a bill of materials. From the description, it sure sounds like that's the type of problem you're trying to solve.
I'm not aware of legislation caused by Anderson, but then I'm not a consultant. Do you mean Sarbannes/Oxley?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2008 at 10:52 am
Grant Fritchey (9/3/2008)
Holy cow, that's a ton of HUGE questions. Most of them can be answered, it depends.To start with, it is possible to have too many relationships, but to determine if you have too many, we'd need to see a lot more of the design and requirements than you've laid out so far. Based on what you described, I don't think you're in any danger.
How to query the database you built is an enormous question. I'd suggest you pick up a copy of Itzik Ben-Gan's book Inside SQL Server 2005: T-SQL Querying. You might also want his other book, T_SQL Programming.
Based on what you've said so far, you might want to do a web search and look at a few designs for a common problem called a bill of materials. From the description, it sure sounds like that's the type of problem you're trying to solve.
I'm not aware of legislation caused by Anderson, but then I'm not a consultant. Do you mean Sarbannes/Oxley?
Sorry, yes these are a lot of questions. I'm trying to get some materials on advanced database design because that is what I am doing. This site seems to be the rest resource so far.
I did mean Sarbannes/Oxley. It came up in some articles I was reading about database design so I was thinking how it may apply to the schema I am designing. I am from the UK so I am not sure if all this applies to me. Nonetheless, you've steered me in the right direction! 🙂
Thanks for all your help though!
September 4, 2008 at 4:43 am
I did mean Sarbannes/Oxley. It came up in some articles I was reading about database design so I was thinking how it may apply to the schema I am designing. I am from the UK so I am not sure if all this applies to me.
SOX is only a legal requirement for a UK company if it has a listing on a US market
September 4, 2008 at 5:33 am
And then only if it's a publicly traded company. I've been working for a private company for years, so I'm only aware of SOX when I hear people complaining about it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2008 at 6:30 pm
I googled Bill Of Materials but couldn't get any details on the problem this is in database design.
I'm looking into replication, but if two databases are not the same in schema, then can replication still exist between them?
Finally, when linking two tables, does it matter which order I draw the lines from?
E.G. I have a business requirement stating one person can own many clothes but clothes can only belong to one person (this is hypothetical).
So in SQL Server, I just drag the lines to connect the two entities. I get the standard lines with just keys at the end. I've noticed that some relationships have like a rotated 8 at the end of the line. What does this mean?
September 5, 2008 at 2:51 am
Bill of Materials (BOM) is the hierarchical structure of how a product is put together.
So, if you have you finished product WidgetA
Which is made up of two PartAs and 1 PartB
And a Part A is made from a PartX, PartY, PartZ
Your BOM table would end up (in basic terms)
Part
PartID PartName ......other columns
1 widgetA
2 PartA
3 PartB
4 PartX
5 PartY
6 PartZ
BOM
PartID SubPartID NumParts Level
1 2 2 1
1 3 1 1
2 4 1 2
2 5 1 2
2 6 1 2
September 8, 2008 at 6:10 am
The rotated 8 is a symbol for infinity. It means that you've defined a one-to-many relationship. That is, one person can have many clothes. But really, in a design of that nature, what you need is many-to-many. This means you define a person table, and a clothes table and then a third table that relates each of the other two through a one-to-many relationship because two people can own the same shirt at the same time.
But this is still nibbling around the edges. You really should read up on one of the fundamentals of database design books first.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply