|
|
Colin Angus Mackay wrote:
so you have a person_owns_company table and a company_employs_person table
Question: Would it be better to add the above two tables or one one table called "PersonCompany" with foreignkeys: PersonID and CompanyID and one field called "RelationType" eg: IsEmployee or IsOwner. (kind of like a crossreference). Of course this would also be two tables if I introduce a separate table called "TypeTable" and use it to lookup the "RelationType". Any suggestions?
|
|
|
|
|
hp108 wrote:
Would it be better to add ... one table called "PersonCompany" with foreignkeys: PersonID and CompanyID and one field called "RelationType"
I wouldn't have that as one table with a RelationType column unless there were going to be many queries that didn't care what the relation was, just that they were related in some way. The reason for this is that these are two distinct logical relationships and the physical side should match unless there are performance problems. In otherwords, design the model as normalised as possible, then denormalise it afterwards if you find performance problems.
If you do decide to put it only one table, remember that the RelationType column is most likely to become part of the Primary Key if you can have a person that shares both types of relation with the company.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I believe you have a point because:
(quick type, excuse mistakes - just to make a point)
select p.*, c.companyname from person p inner join personcompany pc on p.personid = pc.personid
inner join company c on pc.companyid = c.companyid where
pc.relationtype="IsEmployee"
group by c.companyid
would give the same result as the following which does not use the where clause:
select p.*, c.companyname from person p inner join company-employs-person pc on p.personid = pc.personid
inner join company c on pc.companyid = c.companyid
group by c.companyid
I'll experiment with both and see which is faster.Thanks.
|
|
|
|
|
Colin Angus Mackay wrote:
A person can have a homeAddressID column which will be a foreign key to the address table
A company can have an addressID column which, again, will be a foreign key for the address table.
Sorry for the bother! Does this mean that "homeAddressID" in the address table will be null when the address belongs to the company ( and vice versa ) ?
|
|
|
|
|
No, the address table would have an AddressID column which would be its primary key. The Address table does not need to know that it is a home address or office address. It is just an address.
The person table may have a homeAddressId column (indicating that it is the home address of the person). A person may have a workAddressID as well (if that is your design - although it may be inferred by their links to a company or companyLocation)
The Address table would be part of a one-to-many join, for every one address there are many people or many companies or both. The Address table cannot track that, so the tracking goes to the person or company table. Something like this:
ParentTable (Address) ChildTable (Person)
--------------------- -------------------
ParentId <----\ ChildID
OtherColumns \---> ParentID
OtherColumns
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Design wise this makes sense, but in practice this would mean that I have to add the address record before I could refer to it from the person or company table, which seems backward as it would allow me to add addresses without requiring it be used. So I could end up with addresses which are orphaned (as no one refers to them). I am new at this so I do not know if this is normal pattern?
|
|
|
|
|
Normally I use stored procedures for everything, so in a situation like this the stored procedure would determine if an existing address existed and join to that, or if a new address needed to be created and then create and join to the new address. It would do this all in one transaction, so that if the later part of the transaction failed (for example creating a new row in the Person table) then the whole thing would be rolled back and it would be as if there never was an Address record.
In the reverse situation, you could always add a trigger to the person or company/companyLocation table so that in the event that it is being deleted the trigger can see if there is anything else joined to the address and remove the address if it would be orphaned.
Personally, I'm not a big fan of triggers (I've only used them for building audit information) as I prefer all access through stored procedures.
There will always be weak points in a design but you you'll know how you intend to use it and can compromise on certain things. For example, if you will be mostly reading (SELECTing) data then you can optimise it by putting indexes on the columns most often referenced in your WHERE clauses, however, by doing this you will slow down INSERT, UPDATE, DELETES.
I hope this helps (I may have gone off at a tangent a little in my last paragraph).
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanks! You have clarified a number of things that always confused me. Appreciate it.
|
|
|
|
|
I think you are taking normalization too far.
Dont concern yourself as to storing addresses more than once.
The address in each table I create is usually just an attribute.
This means I would have an address for each company and person.
Same with phone.
Although it is possible that a company and person might have the same address or phone it isnt important that they are the same physical record in some table.
"Owning" a company - you work for it.
I see three tables in your entire post.
companies -> companies_people <- people
This way people can be part of a company or NOT.
Company table would have a main phone, fax etc and address(s)
People would have their own ID, address and phone.
YES you might have duplicated data but it certainly could not be called a transitive dependancy.
http://www.jasncab.com/huberblog :: Jason Huber
|
|
|
|
|
Thanks. I agree that the database does not heve to be normalised to the smallest detail as it would effect perfomance and make the queries more complex. But like Colin pointed out:
In otherwords, design the model as normalised as possible, then denormalise it afterwards if you find performance problems.
I want to take this one step further - not denormalise it but use its structure to dynamically create only the required tables/relations.
It might be a good idea to kind of explain what I am trying to do. The model I am trying to build will not actually contain any data, but its structure will be used (schema - which could be in xml format) to generate a database that will only contain the tables and structure required by the user. For example: User may want to store a telephone list - name and all the related phone numbers - so I would pick only the necessary tables (person,phonegroup,phone) and their relationships from the schema and create the database. Then he might want another DB to have all company info...
So the idea is to "morph". For this I need all the conceivable possibility for the schema. Now I know this may not be the best approach for this kind of application and suggestions are welcome.
Thanks!
|
|
|
|
|
HAs anybody out here used the Oracle 10G. I want to know the feasibility related to the this version of oracle and the ASP.NET compatibility with it.
|
|
|
|
|
I use 10G regularly in two environments installed on Windows 2003.
Works just fine. ASP.NET and the driver you currently use will work fine.
http://www.jasncab.com/huberblog :: Jason Huber
|
|
|
|
|
Hi
i have a db on sqlserver 2000;
How i can export tables and relations to a access db ?
I need it cause access has db documentor;
Thanks;
|
|
|
|
|
1) There are lots of good documentation tools for sql. Better yet, try the metadata server in sql.
2) I suppose you could just link the access table to sql server.
|
|
|
|
|
Hi,
You can do it by following the below steps:
1. Create a System DSN, giving the connectivity to the SQL Server database, which u want to import.
2. Create a new Acesss Databe(.mdb)
3. Goto files,select Get External Data -> Import.
4. Now from the file types, select the ODBC databases and locate the dsn whihc u have created.
5. Choose the tables which u want to import and click Ok.
This will import all the selected SQl tables to your Acess databse.
Indus_v
|
|
|
|
|
I have not relationship!!!!
|
|
|
|
|
Hi,
Although i have successfully implemented the Microsoft Data Access Application Blocks with Oracle 8i but i am suspicious reagrding the DAAB compatibility with Oracle 10G.
If anybody has used the DAAB with the Oracle 10g or has the knowledge regarding the same, kindly share it and help me our.
~Indus_v
|
|
|
|
|
hello
I want to use multiple database .They may be on different server?
can you help me about the problems that i will have?can you suggest one article?
|
|
|
|
|
Hi,
You can go for the Database Access Application Blocks, if you want to use multiple databases simultaneously in a single applications.
You can got to the Microsoft site for this:
ttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp
Indus_v
|
|
|
|
|
|
First, in order to answer your question well we need to know what the database technology is and what it will be communicating with. The rest of the answer assumes SQL Server 2000 and .NET
If you are having the databases communicate with each other you can set up linked servers and then refer to them using the 4 part table notation like this:
[server].[database].[schema].[table]
[server].[database].[schema].[view]
[server].[database].[schema].[stored_procedure]
If you are communicating with .NET you can use the normal provider mechanism and open separate connections to each database you need. You can have multiple connections to multiple servers if your application requires it. Your ConnectionString will point to the relevant database and server.
If you need to connect to both databases through one SqlConnection you can link the servers and then connect to the server that has the link (this will be the local server, the server that has been linked to is the remote server). You can then use the 4 part names, see above, to work with the correct data on the correct server.
NOTE: If you are using data on the local server you don't need to use the full 4 part name, however, I've found that when dealing with linked servers that using the 4 part name always reduces ambiguity and makes tracking down bugs easier as I'm not left wondering what connection something was running on and which server is local and which is remote.
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I beleave you shoud create a linked server between database.
select article_id from samcpd05.online.dbo.dimensao_produto
left join samcpd29.onlinehist.dbo.produtos on ....
samcpd29 = server
onlinehist = database
if you need more examples my e-mail is: sadmilson@terra.com.br
|
|
|
|
|
I am running Access 2002.
I want to do something similar to the following:
SELECT * FROM tb1
LEFT JOIN
( SELECT tb2.ID AS tb2_ID, tb2.Author AS tb2_Author, tb2.Address AS tb3_Address
FROM tb2, tb3
WHERE tb2.Address = tb3.ID ) AS sb1
ON sb1.tb2_ID = tb1.Author
How can I do that?
----
to die is gain
|
|
|
|
|
Yes you can really close to what you have already.
You are joining from the sub out. and that should be ok.
Its called a correlated sub query..
The problem is I dont see your point in this particular example. It could be that I am just missing it.
But really you could just rewrite your example
SELECT tb1.namedfields, AS tb2_ID, tb2.Author AS tb2_Author, tb2.Address AS tb3_Address FROM tb1 LEFT JOIN tb2.author ON FROM tb2.id = tb1.id left join tb3 on tb2.Address = tb3.ID
http://www.jasncab.com/huberblog :: Jason Huber
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
